MySQL Cookbook

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 queries that retrieve different levels of summary information. Or use a programming language to do some of the work so that you can use a single query.

Discussion

Sometimes a report involves different levels of summary information. For example, 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.712834718375 |
| Henry |          911 |        42.059095106187 |
| Suzi  |          893 |        41.228070175439 |
+-------+--------------+------------------------+

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. Generating the report in SQL involves a couple of queries, because you can’t calculate a per-group summary and an overall summary in a single query.^[34] First, run a query to get the overall mileage total:

mysql> SELECT @total := SUM(miles) AS 'total miles' FROM driver_log; +-------------+ | total ...