SQL query – split out count() results into one line of GROUP BY results

I want to use GROUP BY to get a row per month-year. Additionally, I have another field which, for this example, is colour and can have values of Blue or Red.

I want to see a table with columns for Blue, Red, month and year = I want to count the number of each colour per month-year. Can I do this with an SQL query?

I know that count(colour) will give me the total number of rows for each month-year.

It’s good if it’s possible to do this but some solutions may involve coding in “Red” and “Blue” = just 2 values. Is it possible to run a query to execute a count which will split out the “answers” from the count into each line rather than a line each?

Example data:

Year	Month	Day	Colour
2021	3	6	Blue
2021	3	7	Blue
2021	3	8	Blue
2021	3	9	Red
2021	4	5	Blue
2021	4	6	Red
2021	4	7	Blue
2021	4	8	Red
2021	4	9	Red

to give result

Year	Month	Blue	Red
2021	3	3	1
2021	4	2	3

I’m doing this in mysql and also in javascript using alasql but a suggestion for any version of SQL will probably be helpful here…

Answer

You can use SUM(CASE WHEN... to do this

SELECT Year, Month, SUM(CASE WHEN Colour = 'Blue' THEN 1 ELSE 0 END) AS Blue, etc
FROM table
GROUP BY Year, Month