# In-CASE you need to SUM...

• The number of times I fall for the UNION for UNION ALL in questions...

• Nice! I remembered the Union ALL clause on time.

• Very good question. I saw the UNION part right away, but I admit I researched the CASE part first as I wasn't quite sure that would work in a SUM statement. I'm happy I got the correct answer without plopping it into SSMS first.

Now waiting to see who says it should be worth two points since there were (sort of) two questions in one... :rolleyes:

• brazumich (7/11/2011)

Now waiting to see who says it should be worth two points since there were (sort of) two questions in one... :rolleyes:

This question wasn't worth two points???

:hehe:

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

• I'd prefer 2 pints...

...to the pub!

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

• Nice question, thanks. Apparently I needed a reminder of the distinction between UNION and UNION ALL

• Thank you for this little question.

I was almost skipping the SELECT...UNION parts...

Fortunately I got it right.

Great QotD for a monday morning. 😀

Best regards,

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

• Can someone explain to me why you might expect the SUM(CASE) construct wouldn't work? I use this all the time and thought it was pretty straightforward T-SQL.

Nice try at tripping us up w/ UNION vs. UNION ALL. You almost got me! 😀 (Or, more accurately, I nearly failed to fully think it through...)

Thanks,

Rich

• rmechaber (7/11/2011)

Can someone explain to me why you might expect the SUM(CASE) construct wouldn't work? I use this all the time and thought it was pretty straightforward T-SQL.

lol although it IS fairly straight forward T-SQL, it's not a commonly used construct I don't think. I've certainly never seen it used in any tutorials etc. Still, 14% of people seem to think it would error! 😀

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

• rmechaber (7/11/2011)

Can someone explain to me why you might expect the SUM(CASE) construct wouldn't work? I use this all the time and thought it was pretty straightforward T-SQL.

Honest answer? I'm always suspicious of potential "gotchas" in the QOTD. And, I've never seen/used it before. Interestingly enough, I am pretty sure that if I hadn't seen the question, at some point I would have tried it in the hope it would solve a problem, and without researching first to see if it should work.

But now that I know...

• brazumich (7/11/2011)

rmechaber (7/11/2011)

Can someone explain to me why you might expect the SUM(CASE) construct wouldn't work? I use this all the time and thought it was pretty straightforward T-SQL.

Honest answer? I'm always suspicious of potential "gotchas" in the QOTD. And, I've never seen/used it before. Interestingly enough, I am pretty sure that if I hadn't seen the question, at some point I would have tried it in the hope it would solve a problem, and without researching first to see if it should work.

But now that I know...

Well, it's an easy way to return subtotals for a data set, including zero for all subtotal categories that have no data points: `SUM(CASE WHEN myval BETWEEN cat_min AND cat_max THEN 1 ELSE 0 END) As CntCatgory` will return a value of 0 for CntCategory if your data set has no rows with myval within the specified range of values.

I think I first saw this used by Ken Henderson, RIP.

Rich

• Good question! The title almost had me fooled too. I breezed right by and figured 47 would be the answer. However, I've been answering these QODs long enough to know that things are not usually as they appear at first. I agree with previous comments. The real lesson here is: "pay attention to detail". As data pros, we need to beat this phrase into our team members skulls (and our own).

Chris Umbaugh
Data Warehouse / Business Intelligence Consultant

• BenWard (7/11/2011)

rmechaber (7/11/2011)

Can someone explain to me why you might expect the SUM(CASE) construct wouldn't work? I use this all the time and thought it was pretty straightforward T-SQL.

lol although it IS fairly straight forward T-SQL, it's not a commonly used construct I don't think. I've certainly never seen it used in any tutorials etc. Still, 14% of people seem to think it would error! 😀

I use CASE often when prototyping fact tables. It is very handy to key dimension keys when the join to the dimension table fails. I've also used it before to guard against divide by zero errors in calculating ratios.

Chris Umbaugh
Data Warehouse / Business Intelligence Consultant