MySQL Cookbook, 2nd Edition

Generating a Report That Includes a Summary and a List

Problem

You want to create a report that displays a summary, together with the list of rows associated with each summary value.

Solution

Use two statements 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 statement.

Discussion

Suppose that you want to produce a report that looks like this:

Name: Ben; days on road: 3; miles driven: 362
  date: 2006-08-29, trip length: 131
  date: 2006-08-30, trip length: 152
  date: 2006-09-02, trip length: 79
Name: Henry; days on road: 5; miles driven: 911
  date: 2006-08-26, trip length: 115
  date: 2006-08-27, trip length: 96
  date: 2006-08-29, trip length: 300
  date: 2006-08-30, trip length: 203
  date: 2006-09-01, trip length: 197
Name: Suzi; days on road: 2; miles driven: 893
  date: 2006-08-29, trip length: 391
  date: 2006-09-02, trip length: 502

The report shows, for each driver in the driver_log table, the following information:

• A summary line showing the driver name, the number of days on the road, and the number of miles driven.

• A list of the dates and mileages for the individual trips from which the summary values are calculated.

This scenario is a variation on the “different levels of summary information” problem discussed in Working with Per-Group and Overall Summary Values Simultaneously. It may not seem like it at first, because one of the types of information is a list rather than a summary. But that’s really just a ...