CAN YOU USE 'CASE WHEN' WITH 'GROUP BY'?

Question

Post reply

CAN YOU USE 'CASE WHEN' WITH 'GROUP BY'?

MarisaG

Old Hand

Points: 333
More actions
July 13, 2014 at 11:07 pm

#287897

Hi All,
Hoping that someone might be able to point me in the right direction.
I'm trying to sum the number of intervals above a cutoff (in this case 57%Fe) but I also want to return a zero where there are no records above 57%.
I can successfully create a query to return the sum of the intervals above 57% as shown below:
SELECT TOP (100) PERCENT Hole_ID, NAT_East, NAT_North, NAT_RL, Max_Depth, SUM(Interval) AS Sum_Intervals
FROM dbo.BCI_Step1_Test
WHERE (Fe_pct > 57)
GROUP BY NAT_East, NAT_North, NAT_RL, Max_Depth, Hole_ID
ORDER BY Hole_ID
But how do I return a zero where there aren't any records above 57%.
I have been trying to use the following case statement but I keep returning the aggregate / group-by error:
CASE WHEN (fe_pct > 57) THEN SUM (interval) ELSE (0) END
I don't have an IT / SQL background so any suggestions as to whether what I'm trying to do is possible would be greatly appreciated.
Thanks, Marisa

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

Maybe something like this:

SELECT Hole_ID, NAT_East, NAT_North, NAT_RL, Max_Depth, SUM(Interval) AS Sum_Intervals

FROM dbo.BCI_Step1_Test

WHERE (Fe_pct > 57)

GROUP BY NAT_East, NAT_North, NAT_RL, Max_Depth, Hole_ID

UNION ALL

SELECT Hole_ID, NAT_East, NAT_North, NAT_RL, Max_Depth, 0 AS Sum_Intervals

FROM dbo.BCI_Step1_Test

WHERE (Fe_pct <= 57)

GROUP BY NAT_East, NAT_North, NAT_RL, Max_Depth, Hole_ID

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

MarisaG Old Hand Points: 333 More actions · Answer 2

Hi SSChampion,

Thanks for the response, much appreciated.

The union does work however it returns 2 records where I only want one. I only want to see the sum of the intervals above 57% & for everything else just 0.

BD0001804654.837561365.06477.441410

BD0002804702.457561452.47475.77320

BD0002804702.457561452.47475.77324

BD0003804738.867561534.75475.77310

BD0004804103.767561485.35475.18290

BD0004804103.767561485.35475.18296

Is there a way that I can tell it in the second part of the statement to exclude the records that were calculated in the first part of the statement?

Many thanks, Marisa

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 3

Well, it would be easier to verify if you provided table DDL, sample data and desired output. (see the first link in my signature on how to do this).

The data you just posted, is that output or input?

By the way, the two queries in the union all are mutually exclusive (one > 57, the other <= 57) so I'm not sure how you got double records.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 4

This might help..

SELECT Hole_ID, NAT_East, NAT_North, NAT_RL, Max_Depth, SUM( CASE WHEN Fe_pct > 57 THEN Interval ELSE 0 END ) AS Sum_Intervals

FROM dbo.BCI_Step1_Test

GROUP BY NAT_East, NAT_North, NAT_RL, Max_Depth, Hole_ID

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

MarisaG Old Hand Points: 333 More actions · Answer 5

Hi Koen,

My apologies, I will need to read your link regarding posts as I am a bit of a novice.

In regards to my last post that was just a snippet of the output. My input table has approximately 160,000 records which I want to group by 5 fields reducing it to approximately 6,000 records.

Each of the 6,000 records is associated with numerous intervals & Fe values which may be both below & above 57% (hence the duplicate records).

Basically where the record has associated Fe values above 57% I want to sum the associated intervals & then I want all of the remaining 6000 records that don't have values greater than 57% to be given an interval of 0.

The confusing part is that all of the records that are associated with values over 57% Fe also have associated records with values below 57% Fe. The first part of the union returns 4,000 records with a summed interval associated with the >57% Fe, but then the second part of the union returns 6,000 records with a 0 as essentially all of the records are associated with values less than 57% Fe.

I will read your link & try to upload more useful information in the morning as the working day has come to an end here in Australia.

Thanks for your feedback.

Cheers, Marisa

MarisaG Old Hand Points: 333 More actions · Answer 6

Hi Kingston,

Thank you very much, that's exactly what I was after.

Such a simple way of doing it, but without your help I don't think I could have managed to get it working by myself.

Koen, thanks for your help too.

Have a great day, Marisa

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 7

MarisaG (7/14/2014)
Hi Kingston,
Thank you very much, that's exactly what I was after.
Such a simple way of doing it, but without your help I don't think I could have managed to get it working by myself.
Koen, thanks for your help too.
Have a great day, Marisa

Glad I could help you out 🙂

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/