Why are my counts different when using CASE operator VS IF function?

user_7718928 0
Viewed 196

I learnt that the difference between CASE and IF statement is as follows "IF is a CASE with only one 'WHEN' statement". But I got confused when querying this:

I have the table Olympics which contains the countries and medals associated to it. I am counting the total number of medals won by each country. The first query results in the correct output but second query does not. Can anyone tell the reason why case-when and if statement are working differently here?

First query

select
    NOC,
    COUNT(IF (medal = "GOLD", medal, NULL)) as GOLD,
    COUNT(IF (medal = "SILVER", medal, NULL)) as SILVER,
    COUNT(IF (medal = "BRONZE", medal, NULL)) as BRONZE
from olympics 
group by NOC;
NOC GOLD SILVER BRONZE
ARG 1 0 0
ARM 0 2 0
AUS 1 1 1

Second query

select NOC,
count(case when MEDAL='GOLD' then '1' ELSE 0 end) as GOLD,
count(case when MEDAL='SILVER' then '1'  ELSE 0 end) as SILVER,
count(case when MEDAL='BRONZE' then '1' ELSE 0 end) as BRONZE
from olympics 
group by NOC
NOC GOLD SILVER BRONZE
ARG 1 1 1
ARM 2 2 2
AUS 3 3 3
mysql sql
1 Answers

You have the COUNT() incorrect because COUNT(<expression>) counts the number of non-NULL values. Your expressions always return a non-NULL value, so all three values would be the same.

If you are using COUNT(), ELSE needs to be NULL for it to work:

count(case when MEDAL = 'GOLD' then 1 end) as GOLD,

(The default is NULL.)

Alternatively, you could use SUM().

I removed the single quotes. It doesn't matter what is being counted. This is Standard SQL, hence recommended (in my opinion) over using the bespoke IF() extension in MySQL.

If you are going to use a SQL extension, you might as well use a more convenient one:

sum( MEDAL = 'GOLD' ) as GOLD

MySQL treats booleans as integers, with "1" for true and "0" for false.

user_11440350
Related