SQL count by type

December 15, 2021

I would like to summarize in a table so that I have for each id in each round,
the number of tokens “red” and “blue”. What would the SQL query for such table?

Here’s the data.

id | round | color | ntokens
1  |   1   | blue  | 5
1  |   2   |  red  | 83
1  |   3   | blue  | 77
2  |   3   | blue  | 3
2  |   3   |  red  | 2
3  |   1   |  red  | 4
3  |   1   | blue  | 55
3  |   2   | blue  | 22

The result will display like below:

id | round | blue_count | red_count
 1 |   1   |      5     |    0
 1 |   2   |      0     |    83
 1 |   3   |     77     |    0
 2 |   3   |      3     |    2
 3 |   1   |     44     |    4
 3 |   2   |     22     |    0

I have been trying the following code:

  SELECT id, round,
  COUNT(color='red') as red_count,
  COUNT(color='blue') as blue_count
  FROM data
  GROUP BY id, round

>Solution :

count counts values that are not null. = returns either true or false, which are both not null. You could use a case expression to create the desired behavior though:

SELECT   id,
         round,
         COUNT(CASE color WHEN 'red' THEN 1 END) as red_count,
         COUNT(CASE color WHEN 'blue' THEN 1 END) as blue_count
FROM     data
GROUP BY id, round