Recipe 8.16. Working with Per-Group and Overall Summary Values Simultaneously


Recipe 8.16. 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. If it's necessary only to display multiple summary levels, WITH ROLLUP might be sufficient.

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.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 | +-------------+ |        2166 | +-------------+ 

Now, 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.7128 | | Henry |          911 |                42.0591 | | Suzi  |          893 |                41.2281 | +-------+--------------+------------------------+ 

To combine the two statements into one, use a subquery that computes the total miles:

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

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 that you want to determine which drivers had a lower average miles per day than the group average. Calculate the overall average in a subquery, and then compare each driver's average to the overall average using a HAVING clause:

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

If you just want to display different summary values (and not perform calculations involving one summary level against another), add WITH ROLLUP to the GROUP BY clause:

mysql> SELECT name, SUM(miles) AS 'miles/driver'     -> FROM driver_log GROUP BY name WITH ROLLUP; +-------+--------------+ | name  | miles/driver | +-------+--------------+ | Ben   |          362 | | Henry |          911 | | Suzi  |          893 | | NULL  |         2166 | +-------+--------------+ mysql> SELECT name, AVG(miles) AS driver_avg FROM driver_log     -> GROUP BY name WITH ROLLUP; +-------+------------+ | name  | driver_avg | +-------+------------+ | Ben   |   120.6667 | | Henry |   182.2000 | | Suzi  |   446.5000 | | NULL  |   216.6000 | +-------+------------+ 

In each case, the output row with NULL in the name column represents the overall sum or average calculated over all drivers.

WITH ROLLUP can present multiple levels of summary, if you group by more than one column. The following statement shows the number of mail messages sent between each pair of users:

mysql> SELECT srcuser, dstuser, COUNT(*)     -> FROM mail GROUP BY srcuser, dstuser; +---------+---------+----------+ | srcuser | dstuser | COUNT(*) | +---------+---------+----------+ | barb    | barb    |        1 | | barb    | tricia  |        2 | | gene    | barb    |        2 | | gene    | gene    |        3 | | gene    | tricia  |        1 | | phil    | barb    |        1 | | phil    | phil    |        2 | | phil    | tricia  |        2 | | tricia  | gene    |        1 | | tricia  | phil    |        1 | +---------+---------+----------+ 

Adding WITH ROLLUP causes the output to include an intermediate count for each srcuser value (these are the lines with NULL in the dstuser column), plus an overall count at the end:

mysql> SELECT srcuser, dstuser, COUNT(*)     -> FROM mail GROUP BY srcuser, dstuser WITH ROLLUP; +---------+---------+----------+ | srcuser | dstuser | COUNT(*) | +---------+---------+----------+ | barb    | barb    |        1 | | barb    | tricia  |        2 | | barb    | NULL    |        3 | | gene    | barb    |        2 | | gene    | gene    |        3 | | gene    | tricia  |        1 | | gene    | NULL    |        6 | | phil    | barb    |        1 | | phil    | phil    |        2 | | phil    | tricia  |        2 | | phil    | NULL    |        5 | | tricia  | gene    |        1 | | tricia  | phil    |        1 | | tricia  | NULL    |        2 | | NULL    | NULL    |       16 | +---------+---------+----------+ 




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net