Working with Per-Group and Overall Summary Values Simultaneously

7.17.1 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.

7.17.2 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.

7.17.3 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.[2] First, run a query to get the overall mileage total:

[2] Well... that's not strictly true. With a subselect, you could generate the summary with a single query. But MySQL won't have subselects until Version 4.1.

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

Then calculate the per-group values and use the overall total to compute the percentages:

mysql> SELECT name, -> SUM(miles) AS 'miles/driver', -> (SUM(miles)*100)/@total AS 'percent of total miles' -> FROM driver_log GROUP BY name; +-------+--------------+------------------------+ | name | miles/driver | percent of total miles | +-------+--------------+------------------------+ | Ben | 362 | 16.712834718375 | | Henry | 911 | 42.059095106187 | | Suzi | 893 | 41.228070175439 | +-------+--------------+------------------------+

A different form of multiple-query solution that doesn't involve a variable is to retrieve the overall summary into another table, then join that with the original table:

mysql> CREATE TEMPORARY TABLE t -> SELECT SUM(miles) AS total FROM driver_log; mysql> SELECT driver_log.name, -> SUM(driver_log.miles) AS 'miles/driver', -> (SUM(driver_log.miles)*100)/t.total AS 'percent of total miles' -> FROM driver_log, t GROUP BY driver_log.name; +-------+--------------+------------------------+ | name | miles/driver | percent of total miles | +-------+--------------+------------------------+ | Ben | 362 | 16.71 | | Henry | 911 | 42.06 | | Suzi | 893 | 41.23 | +-------+--------------+------------------------+

If you're generating the report from within a program, you can do some of the summary math using your programming language and eliminate one of the queries. Here's an example in Python:

# issue query to calculate per-driver totals cursor = conn.cursor ( ) cursor.execute ("SELECT name, SUM(miles) FROM driver_log GROUP BY name") rows = cursor.fetchall ( ) cursor.close ( ) # iterate once through result to calculate overall total miles total = 0 for (name, miles) in rows: total = total + miles # iterate again to print report print "name miles/driver percent of total miles" for (name, miles) in rows: print "%-8s %5d %f" % (name, miles, (100*miles)/total)

Another type of problem that uses different levels of summary information occurs when you want to compare per-group summary values with the corresponding overall summary value. Suppose you want to determine which drivers had a lower average miles per day than the group average. Using only SQL, this task can't be performed with a single query, but you can easily do it with two. First, calculate the overall average and save it in a variable:

mysql> SELECT @overall_avg := AVG(miles) FROM driver_log; +----------------------------+ | @overall_avg := AVG(miles) | +----------------------------+ | 216.6000 | +----------------------------+

Then compare each driver's average to the saved value using a HAVING clause:

mysql> SELECT name, AVG(miles) AS driver_avg FROM driver_log -> GROUP BY name -> HAVING driver_avg < @overall_avg; +-------+------------+ | name | driver_avg | +-------+------------+ | Ben | 120.6667 | | Henry | 182.2000 | +-------+------------+

Just as when producing a report that uses different levels of summary information, you can solve this problem without using two queries if you're writing a program by using your programming language to do some of the work:

1. Issue a query to retrieve the per-group summary information.
2. Iterate through the result set once to calculate the overall summary value.
3. Iterate through the result set again, comparing each per-group summary value to the overall value and displaying only those records for which the comparison succeeds.