CODEWITHSUNDEEP
mysqlcount
George Marshall
George Marshall

Reputation: 59

How to get counts of different values on the same line?

I need a report with the count of the different values a column has, grouped by date, one output line per date.

The table columns are date, location ID (var) and rating (var). Rating can have only one of four values, ‘A’, ‘B’, ‘C’ or null/blank. I can get one line per date per value with this select, but how can I get the counts for all four values on the same line, for each date?

SELECT date, rating, count(rating) AS ratings
FROM table
GROUP BY date, rating

Upvotes: 0

Views: 93

Answers (1)

Mark Byers
Mark Byers

Reputation: 838226

Use SUM with a boolean as follows:

SELECT
    date,
    SUM(rating = 'A') AS count_A,
    SUM(rating = 'B') AS count_B,
    SUM(rating = 'C') AS count_C,
    SUM(rating = '' OR rating IS NULL) AS count_blank
FROM yourtable
GROUP BY date

Upvotes: 2

Related Questions