Double Sum in SQL? - 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

General SQL Server Forums

New to SQL Server Programming

Double Sum in SQL?

Author

Topic

Gerald42
Starting Member

3 Posts

Posted - 2007-07-26 : 03:48:36

Hello all!

I have three columns of data... Test Name, Test Parameter, Test Result.

I have one column that sums all failed tests grouped by Test Name, and Test Parameter

ie, select Test Name, sum(rows of tests that failed) Failed
etc etc
group by Test Name, Test Parameter

But I also want a column that sums only based on Test Name, regardless of test parameter...so should I try to do something like "sum(Failed)" group by Test Name....in some kind of sub query, or what would you suggest? I know there will be duplicate entries.

Thanks for any help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-26 : 05:20:37

select [Test Name], sum(case when status = 'f' then 1 else 0 end) AS Failed
group by [Test Name]
order by [Test Name]

E 12°55'05.76"
N 56°04'39.42"

Gerald42
Starting Member

3 Posts

Posted - 2007-07-26 : 05:47:22

the problem i have is I need one column grouped by test_name, and the other grouped by both test_name and test_parameter

is there some way to work around that? because the sum works fine for either one independently, just not for both in the same query

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-26 : 05:49:33

Read about WITH CUBE and WITH ROLLUP in Books Online. You will benefit from this.
The two keywords are additional parameters to the GROUP BY command.

E 12°55'05.76"
N 56°04'39.42"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-26 : 05:51:27

or this simple

select [Test Name],
COUNT(*) AS Total -- Total For TESTNAME
sum(case when test_Parameter = 'f' then 1 else 0 end) AS Failed1, -- Total For TESTNAME and TEST_PARAMETER f
sum(case when test_Parameter = 'p' then 1 else 0 end) AS Failed2, -- Total For TESTNAME and TEST_PARAMETER p
sum(case when test_Parameter = 'q' then 1 else 0 end) AS Failed3 -- Total For TESTNAME and TEST_PARAMETER q
...
group by [Test Name]
order by [Test Name]

E 12°55'05.76"
N 56°04'39.42"

Gerald42
Starting Member

3 Posts

Posted - 2007-07-26 : 07:55:17

Thanks alot!

That's exactly what i was looking for, the ROLLUP() function did the trick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources