|
I have the following problem. I have two columns. One is a number, the other is a name. I want to make a calculation that sums ONLY where the name is the same. For example:
Name Value CALCULATEDVALUE
Bob 3 9
Bob 2 9
Alice 1 1
Bob 4 9
Carol 2 5
Carol 3 5
How do I go about doing this? I don't have the faintest clue.
Thanks a lot!
Did I post well? Rate it! Did I post badly? Rate that too!
|
|
|
|
|
Use the GROUP BY clause.
SELECT Name, SUM(Value)
FROM MyTable
GROUP BY Name If you need the output to be like the table you have above, you will need to create a subquery and join to it, like this:
SELECT Name, Value, GroupValue
FROM MyTable
INNER JOIN (SELECT Name, SUM(Value) AS GroupValue
FROM MyTable
GROUP BY Name) AS Group ON Group.Name = MyTable.Name
Does this help?
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hello Friends
Someone know some article about "Database SQL Syntax Comparison" among:
- SQL Server 2000
- SQL Server 2005
- Firebird
- Interbase
- MySQL
- PostgreSql
|
|
|
|
|
Oops... I forgot:
- Oracle
Thanks for all!
|
|
|
|
|
Does anyone have an example of code required to connect to an AS400. I am new to .Net and looking for the best way.
Thanks
|
|
|
|
|
|
Does anyone know how to transfer an entire database out of mssql server 2005 (express) into a .sql file so I can recreate it? I have looked though the TSQL commands on msdn and tried bcp (unless I'm missing somthing that will only do tables and I need the stored procedures as well) The reason for this is I want to export the data and put it into a ms sql 2000 database but dts won't work because the database is so different (neither will the sp_attach_db stored procedure (gives an error about indexes)
|
|
|
|
|
I've not had a chance to play around with SQL Server 2005, but in SQL Server 2000 the Enterprise Manager has a feature where you can script database objects (tables, views, stored procedures, user defined functions, etc.) by right clicking on them and selecting the Script item. You can also click on the database to script the whole DB. I'm guessing that SQL Server 2005 will have something similar.
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
that's the problem  the express version of 2005 doesn't have enterprise manager, it has a community built one that doesn't have much funtionality right now. The higher versions of 2005 are suppose to have this but not express (express is the msde of 2005)
|
|
|
|
|
|
it has all the *funtionality* but not the tools (oh and 1 gig ram usage is the other limitation)
|
|
|
|
|
SQL Express Manager is supposed to have this capability.
Download from here[^]. Note that the version of Express Manager needs to match the version of SQL Server Express, so you'll need to upgrade your database server to the April CTP to match the currently-available Express Manager CTP.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
I have that but the problem is it's not in it yet... or if it is I haven't found it...
|
|
|
|
|
Hello:
I get this error when trying to add a series of datarows to a datatable
Any Ideas, Here Is the Code:
Public SaveIt()
{
DataRow dr;
for(int i=0;i<12;i++)
{
dr["Months"] = arrMonths[i]; //Throws Exception
MyDataTable.Rows.Add(dr);
}
MyDatSet.GetChanges();
MyDataSet.WriteXml(strFileName, XmlWriteMode.DiffGram);
}
TIA
GostWrtyer
}
|
|
|
|
|
GostWryter wrote:
I get this error when trying to add a series of datarows to a datatable
"this" has no associated context as you have not actually given an error message.
There is no data row object here.
DataRow dr; does not actually create a DataRow object, only a reference to one, and since you don't reference one then the line fails because you are trying to use a DataRow that does not exist.
To create an appropriate DataRow object use DataTable.NewRow()[^] before trying to use dr. e.g.
dr = MyDataTable.NewRow();
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Colin Angus Mackay wrote:
"this" has no associated context as you have not actually given an error message.
Yes he has, it's in the subject line 
Seriously, while looking at the code, I would expect that it would generate an "Object instance not set to an object" exception instead of that column not found in table.
For GostWryter, yes, the solution given by Colin should fix your problem. A little more explanation. You need to use the DataTable to create a new DataRow so the row contains the table's schema (columns for example), even if the row itself is not in the table yet (that's why you Add it to the Rows collection later.)
-- LuisR
Luis Alonso Ramos
Intelectix - Chihuahua, Mexico
Not much here: My CP Blog!
|
|
|
|
|
|
Hello all:
First...Pardon My Grammar !
Second The call to dr=MyTable.NewRow(); was not there .
Thanks
GostWryter
|
|
|
|
|
in Layout Design,
Is there anyone know the trick, how to put some field(s) into the Page Header Section ?
coz, in every page i wanna show those field(s) shown.
Thank You.
email : sukasukabo@hotmail.com
|
|
|
|
|
Hi
I have a basic question. I want to copy a table in a db to another db (in the destination the table has to be created first with proper columns). the table name and schema will remain same. (I'm using sql server 2000).
Can you provide sample ado.net code, by which I can achieve that?
Thanks
|
|
|
|
|
Aha - so you did post here as well. See my response in the C# forum, you sly dog.
Click here.[^]
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
This method is fine only when source and destination tables are in the same server.
What if they are in different servers?
|
|
|
|
|
hi,
is there any other way of executing a sql script in sql server 2000 besides using the sql query analyzer or the enterprise manager itself?
for example my database server (sql server 2000) is up and running.
what i want do is to change one of the stored procedures. i have made the changes and generated the script.
now is there any way that i could upload this script to the deployed sql server and automatically execute it at some fix time?
please do answer this question, even if the answer is "no" or nobody knows!!
thanx in advance
☺«««DTA»»»☺
|
|
|
|
|
In C:\Program Files\Microsoft SQL Server\80\Tools\Binn you should fine a program called osql.exe. This is a command-line query running tool.
Alternatively, many installation tools (Installshield, wise, windows installer) support some method of executing SQL on a server.
my blog
|
|
|
|
|
thanx a lot Steven. osql.exe was just the utility i needed! works great for me!
One thing that comes into my mind now is that, if sql server 2000 provides such a utility, does this mean that there might be a tool or program for the versioning of all the Stored Procedures, or any objects in the database? if you could refer such a tool/program, it would be Heaven on Earth for me! 
thanx a lot!
☺«««DTA»»»☺
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
