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

John Woo · Accepted Answer · 2018-02-28T16:38:05.063

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.

edited Feb 28 '18 at 16:38

answered Feb 28 '18 at 16:24

John Woo

258,903
69
498
492

This is great! but i still not getting the zero for the Very Good one, because i dont have it in table, but it exist as a grade, by the way, i have a table called 'grades' that have the register of all grades. – Augusto Coelho Henriques Feb 28 '18 at 16:31
this works like a charm thank you very much @John Woo – Augusto Coelho Henriques Feb 28 '18 at 16:41
Can add in an `order by a.id` as well if they need to be in that specific order – abney317 Feb 28 '18 at 16:41

score 0 · Answer 2 · answered Feb 28 '18 at 16:30

0

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

answered Feb 28 '18 at 16:30

mrfred489

80
1
7

same thing from @John Woo i dont have information of 'Very good' one, because its not in the table – Augusto Coelho Henriques Feb 28 '18 at 16:33
You could fix that by having one of each grade in the table by default, and subtracting one from the amount on each grade in the output. – mrfred489 Feb 28 '18 at 16:34
this dont solve the problem, because i **can** or **can't** have the grade, is optional, i cannot subtract 1 from null – Augusto Coelho Henriques Feb 28 '18 at 16:37

Mysql count rows and rows with null

2 Answers2