mysql从where子句获取总值和总数

Question

我正在尝试编写一个 mysql 查询，它将返回列中值的总数以及基于同一列中的 where 子句的值总数。

我有一个这样的表:

+------------------------+-------+
| color                  | code  |
+------------------------+-------+
| red                    |   200 |
| red                    |   202 |
| blue                   |   105 |
| yellow                 |   136 |
| green                  |   561 |
| red                    |   198 |
| blue                   |   414 |
| green                  |   11  |
| yellow                 |   600 |
| green                  |   155 |
| red                    |   865 |
| blue                   |   601 |
| green                  |   311 |
+------------------------+-------+

如果我运行这个查询:

select 
    color, 
    count(*) as count 
from colors 
where code > 0 && 
    code <= 500 
group by color 
order by count(*) desc;

我得到了这个很棒的结果，因为它几乎是我想要的:

+------------------------+-------+
| color                  | count |
+------------------------+-------+
| red                    |     3 |
| green                  |     3 |
| blue                   |     2 |
| yellow                 |     1 |
+------------------------+-------+

我还需要返回列中值的总数，因此结果表将如下所示。

+------------------------+--------------+-------+
| color                  | total        | count |
+------------------------+--------------+-------+
| red                    |            4 |     3 |
| green                  |            4 |     3 |
| blue                   |            3 |     2 |
| yellow                 |            2 |     1 |
+------------------------+--------------+-------+

所以total是color列中每个值的个数，count是匹配where子句的总数。

谢谢:)

这是 SQLFiddle 的链接。

http://sqlfiddle.com/#!9/777f93/2

Answer 1

您需要使用条件聚合来处理计数并让引擎处理总数。

SELECT color
     , count(*) as Total
     , sum(case when code > 0 and code <= 500  then 1 else 0 end) as cnt 
FROM  colors 
GROUP BY color 
ORDER BY cnt desc;