Add COUNT column in SQL output-CodePudding

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;

CodePudding user response：

You need some grouping:

SELECT COUNT(1), mode
  FROM unpopular_songs 
 GROUP BY mode;