Wes Doyle Wes Doyle - 2 months ago 5
SQL Question

SQL find total count of each type in a column

I'm learning SQL and am stumped on what should be a simple query. I have a table with the following pattern:

Id | Type
------------
1 | Red
2 | Blue
3 | Blue
4 | Red
..


I would like to write a query to return a table that counts the total number of instances of each type and returns a table with the following pattern, for example, if 'Blue' occurs in 12 rows, and 'Red' occurs in 16 rows in the table above, the result would be:

Blue | Red
-----------
12 | 16

Answer

You could do it this way:

SELECT Type, COUNT(*) FROM TABLE GROUP BY Type

If you'd like to see the Types in separate columns, you could do this:

SELECT SUM(CASE WHEN Type = 'Blue' THEN 1 ELSE 0 END) AS Blue, SUM(CASE WHEN Type = 'Red' THEN 1 ELSE 0 END) AS Red FROM TABLE
Comments