SQL using CASE in count and group by

aggregate-functions, mysql, sql

Solution

Your problem is that `COUNT` counts every result that is not `NULL`. In your case you are using:

COUNT(distinct case when name = 'sweets' then 1 else 0 end)

So, when the name is not `sweets`, it counts the `0`. Furthermore, since you are using `DISTINCT`, it counts just one or two values. You should either use `SUM` or remove the `DISTINCT` and the `ELSE 0`:

SELECT  DATE(date) as day, 
        COUNT(*),
        SUM(CASE WHEN name = 'fruit' THEN 1 ELSE 0 END) as fruits,
        SUM(CASE WHEN name = 'vege' THEN 1 ELSE 0 END) as vege,
        SUM(CASE WHEN name = 'sweets' THEN 1 ELSE 0 END) as sweets
FROM food
GROUP BY DAY
WITH ROLLUP

Or:

SELECT  DATE(date) as day, 
        COUNT(*),
        COUNT(CASE WHEN name = 'fruit' THEN 1 ELSE NULL END) as fruits,
        COUNT(CASE WHEN name = 'vege' THEN 1 ELSE NULL END) as vege,
        COUNT(CASE WHEN name = 'sweets' THEN 1 ELSE NULL END) as sweets
FROM food
GROUP BY DAY
WITH ROLLUP

Here is a modified sqlfiddle.

Problem

I'm using `CASE` to categorize data in the table and count them but the results aren't accurate live demo [here] ``` select DATE(date) as day, count(*), count(distinct case when name = 'fruit' then 1 else 0 end) as fruits, count(distinct case when name = 'vege' then 1 else 0 end) as vege, count(distinct case when name = 'sweets' then 1 else 0 end) as sweets from food group by day with rollup ``` I'm not sure if the issue is with `CASE` or in the string matching `=` because there's no 'sweets' still it counts 1? any pointers I'd be grateful

Original source