SQL Multiple Count In One Query

Question

我试图运行以下查询以返回包含英语和苏格兰用户计数的所有月份的列表。我不确定如何运行多个计数查询沿彼此。

SELECT month(Date) as month
, COUNT(*) as scottishCount 
FROM users userTable 
WHERE year(Date) = 2015 
AND userTable.UserID in 
(select nationsTable.UserID 
from users nationsTable 
where nationsTable.Nation = 'Scotland'), COUNT(*) as englishCount FROM users userTable WHERE year(Date)=201 AND userTable.UserID in (select nationsTable.UserID from nations nationsTable where nationsTable.Nation= 'England') GROUP BY month(DateClicked);

提前感谢，

弗雷德

Answer 1

必须将条件移到SELECT子句中，以便进行有条件计数。MySQL将true视为1,false视为0，因此您可以使用SUM来计算匹配。似乎没有必要多次从users表中进行选择:

SELECT
MONTH(date) AS month, 
SUM(nation = 'Scotland') AS scottish_count,
SUM(nation = 'England') AS english_count
FROM users
WHERE YEAR(date) = 2015 
GROUP BY MONTH(date)
ORDER BY MONTH(date);