3

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?

Community
  • 1
  • 1
Eilidh
  • 1,270
  • 1
  • 13
  • 33
  • You are already getting the count with the above query so you can manipulate that with your server side script and what do you mean by Colour AS a separate total (e.g. RedCount) ? – Abhik Chakraborty May 27 '14 at 15:15
  • I know I am getting the count of *ALL* colours with the above query. I would like to get the count for a specified colour as a separate total, i.e. returning just the total for that colour rather than returning an array with the counts of all colours. For example, if I have a traffic light that could be Red, Amber, Green, Violet, Pink, Blue, Aquamarine, Brown, Black, White etc. etc. etc. and I only wish to look at Red, Amber and Green, how could I do that without returning the whole array? – Eilidh May 27 '14 at 15:20

3 Answers3

3

For mysql you can do so,by using expression in sum(expr) will result as boolean and you can count the occurrences for your colors individually 

SELECT 
SUM(Colour = 'Red') AS RedCount, 
SUM(Colour = 'Amber') AS AmberCount, 
SUM(Colour = 'Green') AS GreenCount
FROM t

Demo

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
  • 1
    Thank you, that's exactly what I've been looking for! (Apologies if it was an obvious question, I have very little exposure to databases so far and I'm just learning as I go) Thank you. – Eilidh May 27 '14 at 15:21
0

Try this query:

SELECT
  (SELECT COUNT(*) AS ColourCount FROM tableA GROUP BY colour HAVING colour = 'Red') AS     red_lights,
  (SELECT COUNT(*) AS ColourCount FROM tableA GROUP BY colour HAVING colour = 'Green') AS green_lights,
  (SELECT COUNT(*) AS ColourCount FROM tableA GROUP BY colour HAVING colour = 'Amber') AS amber_lights
FROM
  tableA

Here is the Fiddle

mareckmareck
  • 1,560
  • 13
  • 18
-1

You don't have to know the enumerated values:

SELECT Colour, count(*) as Frequency FROM t GROUP BY Colour ;

If your code gets used where 'amber' is reported as 'yellow', it still works.

user7771166
  • 1