Count rows with a specific condition in aggregate query

Question

I have this query to get the number of PlayerSessions with reconnect = TRUE, grouped by 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?

Solution 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

Solution 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)

Solution 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