How to include results of SQL count if count=0?

445 views mysql
6

I have a table with grades (1-6) like so:

mysql> SELECT two FROM data  WHERE date >= '2018-10-23 00:00:00' AND date < '2018-10-23 23:59:59';
+------+
| two  |
+------+
| 5    |
| 1    |
| 1    |
| 2    |
| 1    |
| 2    |
| 2    |
| 1    |
| 1    |
+------+
9 rows in set (0.00 sec)

I need the number of occurrences of each grade like so:

mysql> SELECT two, COUNT(1) as count  from data lookup where date >= '2018-10-23 00:00:00' AND date < '2018-10-23 23:59:59' GROUP BY two;
+------+-------+
| two  | count |
+------+-------+
| 1    |     5 |
| 2    |     3 |
| 5    |     1 |
+------+-------+
3 rows in set (0.00 sec)

BUT I want to include grades, even if they do not exist in the selection, like:

+------+-------+
| two  | count |
+------+-------+
| 1    |     5 |
| 2    |     3 |
| 3    |     0 |
| 4    |     0 |
| 5    |     1 |
| 6    |     0 |
+------+-------+
6 rows in set (0.00 sec)

I've seen solutions with LEFT JOIN and lookup tables, but they are way too complex for my SQL-beginner-head to wrap around.

I have made a lookup table (noten=grades):

mysql> SELECT * from lookup;
+-------+
| noten |
+-------+
|     1 |
|     2 |
|     3 |
|     4 |
|     5 |
|     6 |
+-------+
6 rows in set (0.00 sec)

But I can't get it right...

mysql> SELECT two, COUNT(1) as count  FROM data LEFT JOIN lookup on two=lookup.noten  WHERE date >= '2018-10-23 00:00:
+------+-------+
| two  | count |
+------+-------+
| 1    |     5 |
| 2    |     3 |
| 5    |     1 |
+------+-------+
3 rows in set (0.00 sec)

Your assistance would be much appreciated!!!

answered question

1 Answer

0

  • You can create a all_grades_lookup table within the query itself using Union All, and use it as a Derived Table.
  • You will need to do a Left Join from this lookup table to the data table.
  • Note that when using Left Join(s), any condition(s) on the right-side tables, should be specified in the On clause, instead of Where. If they are specified in Where, it would limit your results, and effectively make it an Inner Join only.

Try the following:

SELECT agl.grade, COUNT(d.two) FROM 
(SELECT 1 AS grade UNION ALL 
 SELECT 2 UNION ALL
 SELECT 3 UNION ALL
 SELECT 4 UNION ALL
 SELECT 5 UNION ALL
 SELECT 6) AS all_grades_lookup AS agl
LEFT JOIN data AS d ON d.two = agl.grade AND 
                  d.date >= '2018-10-23 00:00:00' AND 
                  d.date < '2018-10-23 23:59:59' 
GROUP BY agl.grade

posted this

Have an answer?

JD

Please login first before posting an answer.