I have a 10k row data set where a column, "mode", has either a ‘0’ or ‘1’ integer value. I am attempting to write a query that simply counts the number of ‘0’ and ‘1’ values.
These work but don’t tell the whole story:
SELECT mode FROM unpopular_songs GROUP BY mode;
This shows the mode column with just two rows (0 and 1).
SELECT COUNT(mode) AS minor FROM unpopular_songs WHERE mode = '0';
This shows one column labeled as "MINOR", with one row with a count as 3905. So, this tells part of the story. However, I need another column "MAJOR" that shows the count of values of ‘1’.
This was my best shot at getting the output I wanted, but it’s throwing an error:
SELECT COUNT(mode = '0') AS minor COUNT(mode = '1') AS major FROM unpopular_songs;
You need some grouping:
SELECT COUNT(1), mode FROM unpopular_songs GROUP BY mode;