Using Joins and Subqueries - MySQL - page 505

| 10 | Henry | 2014-07-30 | 203 |
+--------+-------+------------+-------+
A simple summary showing the number of active drivers per day looks like this:
mysql> SELECT trav_date, COUNT(trav_date) AS drivers
-> FROM driver_log GROUP BY trav_date ORDER BY trav_date;
+------------+---------+
| trav_date | drivers |
+------------+---------+
| 2014-07-26 | 1 |
| 2014-07-27 | 1 |
| 2014-07-29 | 3 |
| 2014-07-30 | 2 |
| 2014-08-01 | 1 |
| 2014-08-02 | 2 |
+------------+---------+
Here, the summary category is date, but the summary is “incomplete” in the sense that
it includes entries only for dates represented in the driver_log table. To produce a
summary that includes all categories (all dates within the date range represented in the
table), including those for which no driver was active, create a reference table that lists
each date:
mysql> CREATE TABLE dates (d DATE);
mysql> INSERT INTO dates (d)
-> VALUES('2014-07-26'),('2014-07-27'),('2014-07-28'),
-> ('2014-07-29'),('2014-07-30'),('2014-07-31'),
-> ('2014-08-01'),('2014-08-02');
Then join the reference table to the driver_log table using a LEFT JOIN :
mysql> SELECT dates.d, COUNT(driver_log.trav_date) AS drivers
-> FROM dates LEFT JOIN driver_log ON dates.d = driver_log.trav_date
-> GROUP BY d ORDER BY d;
+------------+---------+
| d | drivers |
+------------+---------+
| 2014-07-26 | 1 |
| 2014-07-27 | 1 |
| 2014-07-28 | 0 |
| 2014-07-29 | 3 |
| 2014-07-30 | 2 |
| 2014-07-31 | 0 |
| 2014-08-01 | 1 |
| 2014-08-02 | 2 |
+------------+---------+
Now the summary includes a row for every date in the range because the LEFT JOIN
forces the output to include a row for every date in the reference table, even those missing
from the driver_log table.