SQL finds the total of each type in a column

Question

I am learning SQL and am at a dead end, which should be a simple query. I have a table with the following template:

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” appears in 12 rows and “Red” in 16 rows in the table above, the result would be:

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

Answer 1

You can do it as follows:

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

If you want to see types in separate columns, you can 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