SQL在count和group by中使用CASE

Question

我使用CASE对表中的数据进行分类和计数，但结果并不准确

实时演示[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

我不确定问题出在CASE还是字符串匹配上，因为没有‘=’，它还是算作1？有什么建议我会很感激的

Answer 1

您的问题是，COUNT会计算每个非NULL的结果。在您的示例中，您使用的是：

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

您可以对别名进行分组：

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

FROM table p

GROUP BY  Year, Month