Menu
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 
746
SELECT COUNT(ALL column_name) FROM table_name; The ALL keyword will count all values in the table including duplicates. You can omit this keyword because the COUNT function uses the ALL keyword as the default whether you write it or not.
Use the COUNT aggregate function to count the number of rows in a table. This function takes the name of the column as its argument (e.g., id ) and returns the number of rows for this particular column in the table (e.g., 5).
SQL – count() with Group By clause The count() function is an aggregate function use to find the count of the rows that satisfy the fixed conditions. The count() function with the GROUP BY clause is used to count the data which were grouped on a particular attribute of the table.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
