MySQL SQL在计数和分组中使用CASE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24636412/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL using CASE in count and group by
提问by Katia
I'm using CASEto categorize data in the table and count them but the results aren't accurate
我正在CASE对表中的数据进行分类并对其进行计数，但结果不准确
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 CASEor in the string matching =because there's no 'sweets' still it counts 1?
any pointers I'd be grateful
我不确定问题是与CASE字符串匹配有关还是在字符串匹配中，=因为没有“甜点”仍然计数为 1？任何指点，我将不胜感激
回答by Lamak
Your problem is that COUNTcounts every result that is not NULL. In your case you are using:
你的问题是COUNT计算每一个不是的结果NULL。在您的情况下，您正在使用：
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 SUMor remove the DISTINCTand the ELSE 0:
因此，当名称不是 时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
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 isa modified sqlfiddle.
这是一个修改后的 sqlfiddle。
回答by Dan Bracuk
You can't group by an alias. You have to group by the expression.
您不能按别名分组。您必须按表达式进行分组。
group by date(date)
回答by Wing
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
