Generating Summaries - MySQL - page 299

For example, to determine how many drivers were on the road and how many miles
were driven each day, group the rows in the driver_log table by date: 1
mysql> SELECT trav_date,
-> COUNT(*) AS 'number of drivers', SUM(miles) As 'miles logged'
-> FROM driver_log GROUP BY trav_date;
+------------+-------------------+--------------+
| trav_date | number of drivers | miles logged |
+------------+-------------------+--------------+
| 2014-07-26 | 1 | 115 |
| 2014-07-27 | 1 | 96 |
| 2014-07-29 | 3 | 822 |
| 2014-07-30 | 2 | 355 |
| 2014-08-01 | 1 | 197 |
| 2014-08-02 | 2 | 581 |
+------------+-------------------+--------------+
However, this per-day summary grows lengthier as you add more rows to the table.
Over time, the number of distinct dates will become so large that the summary fails to
be useful, and you'd probably decide to increase the category size. For example, this
query categorizes by month:
mysql> SELECT YEAR(trav_date) AS year, MONTH(trav_date) AS month,
-> COUNT(*) AS 'number of drivers', SUM(miles) As 'miles logged'
-> FROM driver_log GROUP BY year, month;
+------+-------+-------------------+--------------+
| year | month | number of drivers | miles logged |
+------+-------+-------------------+--------------+
| 2014 | 7 | 7 | 1388 |
| 2014 | 8 | 3 | 778 |
+------+-------+-------------------+--------------+
Now the number of summary rows grows much more slowly over time. Eventually, you
could summarize based only on year to collapse rows even more.
Uses for temporal categorizations are numerous:
• To produce daily summaries from DATETIME or TIMESTAMP columns that have the
potential to contain many unique values, strip the time-of-day part to collapse all
values occurring within a given day to the same value. Any of the following GROUP
BY clauses will do this, although the last one is likely to be slowest:
GROUP BY DATE( col_name )
GROUP BY FROM_DAYS(TO_DAYS( col_name ))
GROUP BY YEAR( col_name ), MONTH( col_name ), DAYOFMONTH( col_name )
GROUP BY DATE_FORMAT( col_name ,'%Y-%m-%e')
1. The result includes an entry only for dates actually represented in the table. To generate a summary with an
entry for the range of dates in the table, use a join to fill in the “missing” values. See Recipe 14.8 .