SQL count by type

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

Answer

I would suggest this to get to the requested result table:

SELECT 
    id, round,
    SUM(CASE WHEN color = 'red' THEN ntokens END) AS red_count,
    SUM(CASE WHEN color = 'blue' THEN ntokens END) AS blue_count
FROM data
GROUP BY id, round