MySQL Cookbook

Generating a Report That Includes a Summary and a List

Problem

You want to write a query that displays a summary, together with the list of records associated with each summary value.

Solution

Recognize that this is a variant on working with different levels of summary information, and solve the problem using the same techniques.

Discussion

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

Name: Ben; days on road: 3; miles driven: 362
  date: 2001-11-29, trip length: 131
  date: 2001-11-30, trip length: 152
  date: 2001-12-02, trip length: 79
Name: Henry; days on road: 5; miles driven: 911
  date: 2001-11-26, trip length: 115
  date: 2001-11-27, trip length: 96
  date: 2001-11-29, trip length: 300
  date: 2001-11-30, trip length: 203
  date: 2001-12-01, trip length: 197
Name: Suzi; days on road: 2; miles driven: 893
  date: 2001-11-29, trip length: 391
  date: 2001-12-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 the previous recipe. 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 “level zero” summary. This kind of problem appears in many other forms: ...