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?

See http://stackoverflow.com/questions/4414539/easiest-way-to-get-a-total-count-and-a-count-of-a-subset for various approaches — kaj, Feb 22 '12 at 12:36

score 73 · Accepted Answer · answered Feb 22 '12 at 12:35

73

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

answered Feb 22 '12 at 12:35

GarethD

68,045
10
83
123

Thanks a lot. Searching for this from 3 hours :) – Temüjin May 28 '20 at 13:56

Simon at The Access Group · Answer 2 · 2012-02-22T12:43:11.567

18

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)

edited Feb 22 '12 at 12:43

answered Feb 22 '12 at 12:36

Simon at The Access Group

6,932
22
45

Hmm, it appears the parentheses don't match up. – Bart van Heukelom Feb 22 '12 at 12:38
Sorry about that, bracket blindness, fixed :) – Simon at The Access Group Feb 22 '12 at 12:40
4

In one quick test, I found that the SUM(IF()) method shown here was faster than the COUNT(CASE) method shown in the accepted answer. – arlomedia Mar 27 '12 at 23:55

score 1 · Answer 3 · answered Feb 22 '12 at 12:36

1

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

answered Feb 22 '12 at 12:36

Lamak

69,480
12
108
116

`ELSE NULL` is redundant, if you do not specify an `ELSE` the result is `NULL`, however this is fairly trivial and I am a fan of using `COUNT` rather than `SUM(CASE WHEN ... THEN 1 ELSE 0 END)` when the desired outcome is a count and not a sum, so you got my vote! Also change COUNY to COUNT... – GarethD Feb 22 '12 at 12:43
@GarethD - I know that is redundant, but it makes it more clear that way – Lamak Feb 22 '12 at 13:08

score 0 · Answer 4 · answered Feb 22 '12 at 12:35

0

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

answered Feb 22 '12 at 12:35

Vikram

8,235
33
47

Count rows with a specific condition in aggregate query

4 Answers4