MySQL 在聚合查询中计算具有特定条件的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9394758/
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
Count rows with a specific condition in aggregate query
提问by Bart van Heukelom
I have this query to get the number of PlayerSessions with reconnect = TRUE, grouped by Player.country:
我有这个查询来获取PlayerSessions的数量reconnect = TRUE，分组为Player.country：
SELECT
country,
COUNT(*) AS with_reconnect
FROM PlayerSession S LEFT JOIN Player P ON (P.id = S.player_id)
WHERE reconnect = TRUE
GROUP BY country
I'd like to modify it to show not just the reconnected session count, but also the total count, something like:
我想修改它以不仅显示重新连接的会话计数，还显示总计数，例如：
SELECT
country,
COUNT(*) AS total,
(COUNT WHERE reconnect = TRUE) AS with_reconnect
FROM PlayerSession S LEFT JOIN Player P ON (P.id = S.player_id)
GROUP BY country
Is this possible, and if so, what is the proper syntax?
这是可能的，如果是，正确的语法是什么？
回答by GarethD
SELECT Country,
COUNT(*) AS Total,
COUNT(CASE WHEN Reconnect = true THEN 1 END) AS With_Reconnect
FROM PlayerSession S
LEFT JOIN Player P
ON P.id = S.player_id
GROUP BY country
回答by Simon at My School Portal
The following will suffice
以下就够了
SELECT
p.country,
COUNT(*) AS total,
SUM(IF(s.reconnect=TRUE,1,0)) AS with_reconnect
FROM PlayerSession s
INNER JOIN Player p
ON p.id = s.player_id
GROUP BY p.country
I just rewrote the query. You'll always have a Player row for every PlayerSession, so changed it to be an INNER JOIN. Also the CONCAT was not needed as there will always be PlayerSession rows in this query (unless there are no sessions)
我刚刚重写了查询。每个 PlayerSession 都会有一个 Player 行，因此将其更改为 INNER JOIN。此外，不需要 CONCAT，因为此查询中总会有 PlayerSession 行（除非没有会话）
回答by Lamak
SELECT
country,
COUNT(CASE WHEN reconnect = TRUE THEN S.player_id ELSE NULL END) AS with_reconnect,
COUNY(*)
FROM PlayerSession S LEFT JOIN Player P ON (P.id = S.player_id)
GROUP BY country
回答by Vikram
SELECT
country,
COUNT(*) AS total,
sum(case when reconnect = TRUE then 1 else 0 end) AS with_reconnect
FROM PlayerSession S LEFT JOIN Player P ON (P.id = S.player_id)
GROUP BY country
