July 11, 2011 at 5:45 am
The number of times I fall for the UNION for UNION ALL in questions...
Must remember to have had caffine before answering QOD!
July 11, 2011 at 5:48 am
Nice! I remembered the Union ALL clause on time.
July 11, 2011 at 6:21 am
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:
July 11, 2011 at 6:26 am
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
July 11, 2011 at 6:28 am
I'd prefer 2 pints...
...to the pub!
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
July 11, 2011 at 7:01 am
Nice question, thanks. Apparently I needed a reminder of the distinction between UNION and UNION ALL
July 11, 2011 at 7:33 am
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
July 11, 2011 at 7:38 am
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
July 11, 2011 at 7:46 am
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
----------------------------------------
July 11, 2011 at 7:58 am
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...
July 11, 2011 at 8:04 am
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
July 11, 2011 at 8:08 am
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
twitter @ToledoSQL
July 11, 2011 at 8:18 am
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
twitter @ToledoSQL
July 11, 2011 at 9:35 am
Nice real-world QOTD. I use case statements like this all the time as I do a lot of custom reporting for our business. I had to look over the code twice before answering because, like many others, I was looking for that added "gotcha". Other than Union vs Union All usage, I didn't see anything that would trip anyone up.
Viewing 15 posts - 16 through 30 (of 45 total)
You must be logged in to reply to this topic. Login to reply