MySQL - 使用 GROUP BY 的条件计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9560432/
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
MySQL - Conditional COUNT with GROUP BY
提问by Jay
I'm trying to refine a query that I am currently using:
我正在尝试优化我目前正在使用的查询：
SELECT `puid`,
COUNT(DISTINCT `droid_v`) AS DROID,
COUNT(DISTINCT `sig_v`) AS sig,
SUM(NoExt) AS hits
FROM temp
GROUP BY `puid`
And I need to get it to only count droid_vwhere droid_Vis greater than 0. Is it possible to condition the count in this way? At the moment, it is counting zero values as countable, and I can't really change the zeros to nullvalues.
我需要得到它只能算droid_v其中droid_V大于0是否有可能以调节这样的计数？目前，它正在将零值计算为可数，我无法真正将零更改为null值。
I do not need to know the count value for droid_V= 0, I only need to count it if it has a number greater than 0. That number will always be either 0, 1, 2, 3, or 4.
我不需要知道droid_V= 0的计数值，我只需要计算大于 0 的数字。该数字将始终为 0、1、2、3 或 4。
I have tried:
我试过了：
SELECT `puid`,
COUNT(DISTINCT CASE WHEN `droid_v` > 0 THEN 1 END) AS DROID,
COUNT(DISTINCT `sig_v`) AS sig,
SUM(`NoExt`) AS hits
FROM temp
GROUP BY `puid`
But this gives a binary result (either 0 or 1) not the count I am expecting.
但这给出了一个二进制结果（0 或 1），而不是我期望的计数。
example output =
示例输出 =
puid DROID sig hits
No PUID 1 1 252
x-fmt/92 1 5 1008
anticipated output:
预期输出：
puid DROID sig hits
No PUID 1 1 252
x-fmt/92 3 5 1008
Sample data:
样本数据：
id;puid;droid_v;sig_v;speed;Ext;NoExt;tally
1;"No PUID";"3";"v13";"SLOW";"0";"63";"63"
2;"x-fmt/92";"3";"v13";"SLOW";"63";"0";"63"
3;"x-fmt/92";"3";"v37";"SLOW";"63";"63";"126"
4;"x-fmt/92";"3";"v45";"SLOW";"63";"63";"126"
5;"x-fmt/92";"3";"v49";"SLOW";"63";"63";"126"
6;"x-fmt/92";"3";"v50";"SLOW";"63";"63";"126"
7;"x-fmt/92";"5";"v13";"SLOW";"63";"0";"63"
8;"No PUID";"5";"v13";"SLOW";"0";"63";"63"
9;"x-fmt/92";"5";"v37";"SLOW";"63";"63";"126"
10;"x-fmt/92";"5";"v45";"SLOW";"63";"63";"126"
11;"x-fmt/92";"5";"v49";"SLOW";"63";"63";"126"
12;"x-fmt/92";"5";"v50";"SLOW";"63";"63";"126"
13;"No PUID";"6";"v13";"FAST";"0";"63";"63"
14;"x-fmt/92";"6";"v13";"SLOW";"63";"0";"63"
15;"No PUID";"6";"v13";"SLOW";"0";"63";"63"
16;"x-fmt/92";"6";"v13";"FAST";"63";"0";"63"
17;"x-fmt/92";"6";"v37";"SLOW";"63";"63";"126"
18;"x-fmt/92";"6";"v37";"FAST";"63";"63";"126"
19;"x-fmt/92";"6";"v45";"FAST";"63";"63";"126"
20;"x-fmt/92";"6";"v45";"SLOW";"63";"63";"126"
21;"x-fmt/92";"6";"v49";"FAST";"63";"63";"126"
22;"x-fmt/92";"6";"v49";"SLOW";"63";"63";"126"
23;"x-fmt/92";"6";"v50";"FAST";"63";"63";"126"
24;"x-fmt/92";"6";"v50";"SLOW";"63";"63";"126"
回答by David Faber
If droid_v can only be 0, 1, 2, 3, or 4, then COUNT(DISTINCT) will never return more than 5, since there are only five possible values. Is that what you want? If so, then try this:
如果 droid_v 只能是 0、1、2、3 或 4，那么 COUNT(DISTINCT) 将永远不会返回超过 5，因为只有五个可能的值。那是你要的吗？如果是这样，那么试试这个：
SELECT puid, COUNT(DISTINCT CASE WHEN droid_v > 0 THEN droid_v ELSE 0 END) - 1 AS droid /* -1 for the case where droid_v is 0 */
, COUNT(DISTINCT sig_v) AS sig
, SUM(NoExt) AS hits
Update:Oops, sorry, the above is not quite right as there might not bea zero. It should be:
更新：哎呀，对不起，上面是不完全正确，因为可能不会是零。它应该是：
SELECT puid, COUNT(DISTINCT CASE WHEN droid_v > 0 THEN droid_v END) AS droid
If, on the other hand, you want a count of all the rows where droid_v > 0, then I think you want this:
另一方面，如果您想要对 droid_v > 0 的所有行进行计数，那么我认为您想要这个：
SELECT puid, SUM(CASE WHEN droid_v > 0 THEN 1 ELSE 0 END) AS droid
, COUNT(DISTINCT sig_v) AS sig
, SUM(NoExt) AS hits
Hope this helps.
希望这可以帮助。
回答by Rene Weteling
Just use a sum
只需使用总和
so if you want to group something check this out
所以如果你想把一些东西分组，看看这个
select city, sum( case when gender = 'male' then 1 else 0 end ) as male,
sum( case when gender = 'female' then 1 else 0 end ) as female
from person
group by city
simple as this :D
就这么简单：D
回答by John Pick
SELECT
`puid`,
COUNT(DISTINCT CASE WHEN `droid_v` > 0 THEN `droid_v` END) AS DROID,
COUNT(DISTINCT `sig_v`) AS sig,
SUM(NoExt) AS hits
回答by Mosty Mostacho
This is the simplest solution I can think of:
这是我能想到的最简单的解决方案：
SELECT puid,
SUM(droid_v > 0) AS DROID,
COUNT(DISTINCT sig_v) AS sig,
SUM(`NoExt`) AS hits
FROM t
GROUP BY puid
However, it is not clear if you want to count the distinct or not. If you want to count the distinct values then:
但是，不清楚是否要计算不同的值。如果要计算不同的值，则：
SELECT puid,
COUNT(if(droid_v > 0, droid_v, null)) AS DROID,
COUNT(DISTINCT sig_v) AS sig,
SUM(`NoExt`) AS hits
FROM t
GROUP BY puid
回答by Randy
add this to the query
将此添加到查询中
WHERE droid_v > 0
