Mysql count rows and rows with null

Question

Hy Guys, I have a table like that:

+----+------+
| id | grade| 
+----+------+
|  1 |    1 |
|  2 |    1 |
|  3 |    2 |
|  4 | NULL |
|  5 | NULL |
|  6 | NULL |
+----+------+

Where

1 Bad

2 Good

3 Very Good

Im trying to get a result something like that:

+--------------+------+
| grade        | count| 
+--------------+------+
| "Bad"        |    2 |
| "Good"       |    1 |
| "Very Good"  |    0 |
| "Not Ranked" |    3 |
+--------------+------+

Im tryng with count but no success

Answer 1

You can use CASE to change the value of grade ibno your desired string value,

SELECT  CASE WHEN grade = 1 THEN 'Bad'
            WHEN grade = 2 THEN 'Good'
            WHEN grade = 3 THEN 'Very Good'
            ELSE 'Not Ranked'
        END as grade,
        COUNT(IFNULL(grade, 0)) as `count`
FROM    TableName
GROUP   BY CASE WHEN grade = 1 THEN 'Bad'
            WHEN grade = 2 THEN 'Good'
            WHEN grade = 3 THEN 'Very Good'
            ELSE 'Not Ranked'
        END

Since you wanted to display all values, you need to create a subquery that returns all values and join to your table using LEFT JOIN.

SELECT  a.grade,
        COUNT(b.id)
FROM    
        (
            SELECT 1 id, 'Bad' grade UNION ALL
            SELECT 2 id, 'Good' grade UNION ALL
            SELECT 3 id, 'Very Good' grade UNION ALL
            SELECT 999 id, 'Not Ranked' grade 
        ) a
        LEFT JOIN TableName b ON a.id = IFNULL(b.grade, 999)
GROUP   BY a.grade

Here's a Demo.

Answer 2

If you can use the numbers, and match them with the string later, then this could be a solution:

SELECT grade, count(*) as id FROM tbl GROUP BY grade

source