SQL using CASE in count and group by

Question

I use CASEto categorize the data in the table and count them, but the results are inaccurate

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 problem is related to CASEor in the line corresponding =, because there are no "sweets" there, is still considered 1? any pointers i would appreciate

Answer 1

Your problem is that it COUNTcounts every result that is not NULL. In your case, you use:

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

, sweets, 0. , DISTINCT, . SUM, DISTINCT 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

:

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

sqlfiddle.

Answer 2

. .

group by date(date)

Answer 3

You can group an alias:

SELECT 
     FROM_UNIXTIME(UnixTimeField, '%Y') AS 'Year'
     ,FROM_UNIXTIME(UnixTimeField, '%m') AS 'Month'

FROM table p

GROUP BY  Year, Month