I'm hoping someone can help me with a SQL select statement for the following problem:
I have the following data in a table:
| date | color |
|---|---|
| 01/23/2023 | RED |
| 01/23/2023 | BLUE |
| 01/23/2023 | GREEN |
| 01/23/2023 | GREEN |
| 01/23/2023 | RED |
| 01/23/2023 | BLUE |
| 01/23/2023 | GREEN |
| 01/24/2023 | BLUE |
| 01/24/2023 | GREEN |
| 01/24/2023 | BLUE |
| 01/24/2023 | RED |
| 01/25/2023 | GREEN |
| 01/25/2023 | BLUE |
| 01/25/2023 | GREEN |
| 01/25/2023 | BLUE |
and I would like to produce this:
| date | RED | BLUE | GREEN |
|---|---|---|---|
| 01/23/2023 | 2 | 2 | 3 |
| 01/24/2023 | 1 | 2 | 1 |
| 01/25/2023 | 0 | 2 | 2 |
I'll have the exact colors so I don't need that to be determined. Sorry but I don't have any idea of how to even to beginning writing this select statement.
Thanks.
CodePudding user response：
SELECT date,
SUM(CASE WHEN color = 'RED' THEN 1 ELSE 0 END) AS RED,
SUM(CASE WHEN color = 'BLUE' THEN 1 ELSE 0 END) AS BLUE,
SUM(CASE WHEN color = 'GREEN' THEN 1 ELSE 0 END) AS GREEN
FROM table1
GROUP BY date
ORDER BY date;
CodePudding user response：
You can use CASE WHEN SUM
SELECT
edate,
SUM (CASE WHEN color = 'RED' THEN 1 ELSE 0 END) AS RED,
SUM (CASE WHEN color = 'BLUE' THEN 1 ELSE 0 END) AS BLUE,
SUM (CASE WHEN color = 'GREEN' THEN 1 ELSE 0 END) AS GREEN
FROM tablename
GROUP BY edate