CODEWITHSUNDEEP
mysqlsqlaggregate-functions
Katia
Katia

Reputation: 729

SQL using CASE in count and group by

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

Upvotes: 13

Views: 53043

Answers (3)

Wing
Wing

Reputation: 1

You can group on an Alias:

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

FROM table p

GROUP BY  Year, Month

Upvotes: -2

Lamak
Lamak

Reputation: 70638

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.

Upvotes: 31

Dan Bracuk
Dan Bracuk

Reputation: 20804

You can't group by an alias. You have to group by the expression.

group by date(date)

Upvotes: 2

Related Questions