Database Reference
In-Depth Information
• To produce monthly or quarterly sales reports, group by
MONTH(
col_name
)
or
QUAR
TER(
col_name
)
to place dates into the correct part of the year.
• To summarize web server activity, store your server's logs in MySQL and run state‐
ments that collapse the rows into different time categories.
Recipe 20.14
discusses
how to do this for Apache.
8.13. Working with Per-Group and Overall Summary
Values Simultaneously
Problem
You want to produce a report that requires different levels of summary detail. Or you
want to compare per-group summary values to an overall summary value.
Solution
Use two statements that retrieve different levels of summary information. Or use a
subquery to retrieve one summary value and refer to it in the outer query that refers to
other summary values. For applications that only display multiple summary levels
(rather than perform additional calculations on them),
WITH
ROLLUP
might be sufficient.
Discussion
Some reports involve multiple levels of summary information. The following report
displays the total number of miles per driver from the
driver_log
table, along with
each driver's miles as a percentage of the total miles in the entire table:
+-------+--------------+------------------------+
| name | miles/driver | percent of total miles |
+-------+--------------+------------------------+
| Ben | 362 | 16.7128 |
| Henry | 911 | 42.0591 |
| Suzi | 893 | 41.2281 |
+-------+--------------+------------------------+
The percentages represent the ratio of each driver's miles to the total miles for all drivers.
To perform the percentage calculation, you need a per-group summary to get each
driver's miles and also an overall summary to get the total miles. First, run a query to
get the overall mileage total:
mysql>
SELECT @total := SUM(miles) AS 'total miles' FROM driver_log;
+-------------+
| total miles |
+-------------+
