在聚合查询中计算具有特定条件的行

I have this query to get the number of PlayerSessions with reconnect = TRUE, grouped by Player.country:

我有这个查询来获取具有reconnect = TRUE的PlayerSessions的数量，按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?

这是可能的，如果是这样，那么正确的语法是什么？

4 个解决方案

#1

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

#2

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行（除非没有会话）

#3

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

#4

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