Selective Summation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Selective Summation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-11-15 : 08:41:26

Lee writes "I want to sum a column selectively, i.e. not the whole column but certain entries in the column based on some conditions (e.g. combination of 3 other columns in the table).

Pseudo code:
Sum(column 4) for all similar (column1, column2, column3) combinations
Return 1 row with unique column1, column2, column3 combination"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-15 : 09:17:21

Not sure exactly what you mean, but it sounds like:

SELECT Column1, Column2, Column3, SUM(Column4) as Total
FROM
Table
GROUP BY Column1, Column2, Column3

It doesn't return 1 row total, but it does return 1 row per combination of columns 1 - 3.

burbakei
Yak Posting Veteran

80 Posts

Posted - 2002-11-15 : 23:31:13

if you want to get one row to sum all combinations of columns1,2,3
you can write

SELECT SUM(Total)
FROM (SELECT Column1, Column2, Column3, SUM(Column4) AS Total
FROM Table
GROUP BY Column1, Column2, Column3)

ConfusedOfLife
Starting Member

35 Posts

Posted - 2002-11-16 : 16:26:58

quote:

SELECT SUM(Total)
FROM (SELECT Column1, Column2, Column3, SUM(Column4) AS Total
FROM Table
GROUP BY Column1, Column2, Column3)

I think that your inner table is wrong, since you didn't close the last parenthesis and also you're using cloumn1, column2 and 3 in the inner table + an aggregate function and I think coz you didn't bring the first 3 columns in the order by, it doesn't work...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-16 : 18:49:14

I'm not sure what either of you guys are talking about .... a SELECT statment, with 1 SUM() and no group by's, returns only 1 record with 1 value. There would be no need for a subquery at all....

the last query (assuming the syntax is worked to be correct) is the equivalent of:

SELECT SUM(Column4) FROM Table

There is no need for the subquery giving all combinations. It is just a total of Column4.

ConfusedOfLife
Starting Member

35 Posts

Posted - 2002-11-18 : 15:04:34

I'm not sure either! :D
I didn't say that your code is wrong dear! I just said that I "think" burbakei is wrong!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources