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
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.
order by a.id as well if they need to be in that specific orderIf 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
