MySQL Cookbook

7.18 Generating a Report That Includes a Summary and a List

7.18.1 Problem

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

7.18.2 Solution

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

7.18.3 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:

• You have a database that lists contributions to candidates in your political party. The party chair requests a printout that shows, for each candidate, the number of contributions and total amount contributed, as well as a list of contributor names and addresses.

• You want to make a handout for a company presentation that summarizes total sales per sales region, with a list under each region showing the sales for each state in the region.

In each case, the solutions are like those discussed in the previous recipe:

• Run separate queries to get the information for each level of detail that you require. (Just as a single query won't produce per-group summary values and an overall summary value at the same time, neither will one query produce per-group summary values and a list of each group's individual records.)

• Fetch the rows that make up the lists and perform the summary calculations yourself to eliminate the summary query.

Let's use each approach to produce the driver report shown at the beginning of this section. The following implementation (in Python) generates the report using one query to summarize the days and miles per driver, and another to fetch the individual trip records for each driver:

# select total miles per driver and construct a dictionary that
# maps each driver name to days on the road and miles driven
name_map = { }
cursor = conn.cursor ( )
cursor.execute ("""
                    SELECT name, COUNT(name), SUM(miles)
                    FROM driver_log GROUP BY name
                """)
for (name, days, miles) in cursor.fetchall ( ):
    name_map[name] = (days, miles)

# select trips for each driver and print the report, displaying the
# summary entry for each driver prior to the list of trips
cursor.execute ("""
                    SELECT name, trav_date, miles
                    FROM driver_log ORDER BY name, trav_date
                """)
cur_name = ""
for (name, trav_date, miles) in cursor.fetchall ( ):
    if cur_name != name:    # new driver; print driver's summary info
        print "Name: %s; days on road: %d; miles driven: %d" \
                        % (name, name_map[name][0], name_map[name][1])
        cur_name = name
    print "  date: %s, trip length: %d" % (trav_date, miles)
cursor.close ( )

By performing summary calculations in the program, you can reduce the number of queries required. If you iterate through the trip list and calculate the per-driver day counts and mileage totals yourself, a single query suffices:

# get list of trips for the drivers
cursor = conn.cursor ( )
cursor.execute ("""
                    SELECT name, trav_date, miles FROM driver_log
                    ORDER BY name, trav_date
                """)
rows = cursor.fetchall ( )
cursor.close ( )

# iterate through rows once to construct a dictionary that
# maps each driver name to days on the road and miles driven
# (the dictionary entries are lists rather than tuples because
# we need mutable values that can be modified in the loop)
name_map = { }
for (name, trav_date, miles) in rows:
    if not name_map.has_key (name): # initialize entry if nonexistent
        name_map[name] = [0, 0]
    name_map[name][0] = name_map[name][0] + 1       # count days
    name_map[name][1] = name_map[name][1] + miles   # sum miles

# iterate through rows again to print the report, displaying the
# summary entry for each driver prior to the list of trips
cur_name = ""
for (name, trav_date, miles) in rows:
    if cur_name != name:    # new driver; print driver's summary info
        print "Name: %s; days on road: %d; miles driven: %d" \
                        % (name, name_map[name][0], name_map[name][1])
        cur_name = name
    print "  date: %s, trip length: %d" % (trav_date, miles)

Should you require more levels of summary information, this type of problem gets more difficult. For example, you might want the report showing driver summaries and trip logs to be preceded by a line that shows the total miles for all drivers:

Total miles driven by all drivers combined: 2166

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

In this case, you need either another query to produce the total mileage, or another calculation in your program that computes the overall total.