Jump to content

Results differ when using CASE & IF Vs. putting the condition in the where clause


Nic.

Recommended Posts

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

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

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...