CODEWITHSUNDEEP
mysqldatabase
user3487681
user3487681

Reputation: 149

COUNT data using case when with mysql

SELECT NAMA_DUN, 
COUNT(case when ((RIGHT(noMyKid, 1))% 2) = 0 then 1 else 0 end) AS FEMALE, 
COUNT(case when ((RIGHT(noMyKid, 1))% 2) = 1 then 1 else 0 end) AS MALE, 
COUNT(DISTINCT(noMyKid)) as jumlah
FROM mohon
LEFT JOIN dun ON dun.KOD_DUN=mohon.dun_nama
WHERE status_proses =  'diproses'
AND concat('20', substr(noMyKid, 1, 2)) = '2008'
AND status_mohon =  'Layak'
AND status_semak =  '1'
AND (
status_bayar =  ''
OR status_bayar =  'Belum'
OR status_bayar =  'Sudah')
AND (
status_terima =  ''
OR status_terima =  'Terima'
) GROUP BY dun_nama
ORDER BY NAMA_DUN

This is my mysql code. why is my 'COUNT CASE WHEN' give the same output for female and male column.

Upvotes: 1

Views: 106

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35573

The COUNT() function does only recognize non-null values, so when using case expressions such as you have here, you can explicitly return NULL where needed

SELECT NAMA_DUN, 
COUNT(case when ((RIGHT(noMyKid, 1))% 2) = 0 then 1 else NULL end) AS FEMALE, 
COUNT(case when ((RIGHT(noMyKid, 1))% 2) = 1 then 1 else NULL end) AS MALE, 
COUNT(DISTINCT(noMyKid)) as jumlah

OR, implicitly return NULL by ignoring the else condition

SELECT NAMA_DUN, 
COUNT(case when ((RIGHT(noMyKid, 1))% 2) = 0 then 1 end) AS FEMALE, 
COUNT(case when ((RIGHT(noMyKid, 1))% 2) = 1 then 1 end) AS MALE, 
COUNT(DISTINCT(noMyKid)) as jumlah

OR, mimic the effect of count by using SUM() provided you do use 1 and 0 (or 1 and NULL)

SELECT NAMA_DUN, 
SUM(case when ((RIGHT(noMyKid, 1))% 2) = 0 then 1 else 0 end) AS FEMALE, 
SUM(case when ((RIGHT(noMyKid, 1))% 2) = 1 then 1 else 0 end) AS MALE, 
COUNT(DISTINCT(noMyKid)) as jumlah

Upvotes: 1

ForguesR
ForguesR

Reputation: 3618

Usually COUNT() is used to count rows and is therefore very often used in the form COUNT(*). When you use a field (or anything else) as a parameter into the COUNT() it counts 1 for every no NULL value.

In your case all your values are not NULL (they are either 1 or 0) and consequently you end up with the same results.

So Abhik Chakraborty is right, use SUM() and everything should be fine.

Upvotes: 2

Related Questions