SQL count by type-CodePudding

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

CodePudding user response：

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

CodePudding user response：

You have to use a CASE in the COUNT.

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

CodePudding user response：

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