Nic. Posted November 12, 2014 Share Posted November 12, 2014 Am I misusing the CASE & IF functions in some way? I expected each query to return the exact same value 1327.73. select sum(ordertotal) as Dec_Sales from orderexport where orderstatus = 'declined' RESULT = 1327.73 select CASE when orderstatus = 'declined' then sum(ordertotal) else NULL end as Dec_Sales FROM orderexport RESULT = {null} select if(orderstatus = 'declined',sum(ordertotal),0) as Dec_Sales FROM orderexport RESULT = 0 Link to comment Share on other sites More sharing options...
justsomeguy Posted November 12, 2014 Share Posted November 12, 2014 It's more of a misuse of the sum function. Aggregate functions like that typically aren't used in case or if statements. For example, in the if statement you have, there's no instruction to tell it to only sum the rows where orderstatus is declined, just a sum of all of the rows. The first query is how you tell it which rows to sum. Link to comment Share on other sites More sharing options...
Nic. Posted November 12, 2014 Author Share Posted November 12, 2014 My CASE and IF work perfectly when I remove the sum. (Dec_Sales values returned in the 35 rows adds up to the $ I was looking for). I didn't even think to test them that way ... Thanks for the speedy response! I'm off to learn how to properly use aggregate functions. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now