MySQL: Count occurrences of known (or enumerated) distinct values
- by Eilidh
After looking at how to count the occurrences of distinct values in a field, I am wondering how to count the occurrences of each distinct value if the distinct values are known (or enumerated).
For example, if I have a simple table -
TrafficLight Colour
------------ ------
1 Red
2 Amber
3 Red
4 Red
5 Green
6 Green
where one column (in this case Colour) has known (or enumerated) distinct values, how could I return the count for each colour as a separate value, rather than as an array, as in the linked example.
To return an array with a count of each colour (using the same method as in the linked example), the query would be something like SELECT Colour COUNT(*) AS ColourCount FROM TrafficLights GROUP BY Colour, and return an array -
Colour ColourCount
------ -----------
Red 3
Amber 1
Green 2
What I would like to do is to return the count for each Colour AS a separate total (e.g. RedCount). How can I do this?
