单个查询中的不同计数

Question

>我有这样的表格：

----------------------- | 姓名 |日期 | ----------------------- |约翰 |七月 | |卡尔 |八月 | |约翰 |七月 | |罗伯特 |八月 | |卡尔 |九月 | |约翰 |八月 | |卡尔 |八月 | |约翰 |七月 | |罗伯特 |九月 | |卡尔 |八月 | -----------------------

我想按月计算名字。

选择名称， COUNT(IF('Date' = 'July '，1,0(( AS 七月， COUNT(IF('Date' = 'August'，1,0(( AS 八月， COUNT(IF('Date' = 'september'，1,0(( AS 九月， 计数 (*( 作为 全部来自表 按名称分组

我尝试了这个查询，但计数值都是一样的

Answer 1

发生这种情况的原因很简单：COUNT()只是计算值的出现次数。(= 数据集中有多少个值？

从这个角度来看，0就像1一样，只是另一个价值。

您可以执行以下操作之一：

SELECT Name, 
COUNT(IF(`Date` = 'July', 1, NULL)) AS July,
COUNT(IF(`Date` = 'August', 1, NULL)) AS August,
COUNT(IF(`Date` = 'September', 1, NULL)) AS September,
COUNT(*) AS All FROM table
GROUP BY Name

这是因为像COUNT()这样的聚合函数会忽略NULL值。

或者，正如其他人回答的那样，您可能只SUM()所有1值：

SELECT Name, 
SUM(`Date` = 'July') AS July,
SUM(`Date` = 'August') AS August,
SUM(`Date` = 'September') AS September,
COUNT(*) AS All FROM table
GROUP BY Name

这写得更短，因为如果评估field = value匹配，则返回1，否则返回 0。无需将IF()包裹起来即可执行完全相同的操作。

Answer 2

SELECT name,
SUM(IF(`month` = 'July',1,0)) AS July,
SUM(IF(`month` = 'August',1,0)) AS August,
SUM(IF(`month` = 'September',1,0)) AS September,
COUNT(*)  FROM test
GROUP BY name;

参考： https://stackoverflow.com/a/13075582/1688441

Answer 3

最简单的方法可以试试这个。具有布尔值(0 或 1(的条件聚合函数

SELECT Name, 
SUM(`Date` = 'July') AS July,
SUM(`Date` = 'August') AS August,
SUM(`Date` = 'September') AS September,
COUNT(*) AS All 
FROM table
GROUP BY Name

Answer 4

COUNT

计算非空值，请尝试SUM

SELECT Name, 
SUM(IF(`Date` = 'July',1,0)) AS July,
SUM(IF(`Date` = 'August',1,0)) AS August,
SUM(IF(`Date` = 'September',1,0)) AS September,
COUNT(*) AS All FROM table
GROUP BY Name

Answer 5

count计算未null的值 - 包括零。有了这些ifs，您可以使用sum来模拟count- 您将有效地计算1s的值：

SELECT Name, 
SUM(IF(`Date` = 'July',1,0)) AS July,
SUM(IF(`Date` = 'August',1,0)) AS August,
SUM(IF(`Date` = 'September',1,0)) AS September,
SUM(*) AS All FROM table
GROUP BY Name

Answer 6

select date, count(*) 
from table1
group by date

应该给你类似的东西

|DATE       |Count(*)|
----------------------
|August     |    5   |
|July       |    3   |
|September  |    2   |

http://sqlfiddle.com/#!9/c0a483/2

Answer 7

尝试SELECT Name, Date, Count(*) as Count FROM table GROUP BY Name,Date

它应该返回如下内容：

| Name  | Date      | Count |
-----------------------------
| Carl  | August    |   3   |
| Carl  | September |   1   |
| John  | July      |   3   |
| John  | August    |   1   |
| Robert| September |   1   |
| Rober | August    |   1   |

请参阅此 SQL 小提琴。