Recipe 8.17. 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 Section 8.16. 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.

Such problems can be solved using a couple of approaches:

  • Run separate statements to get the information for each level of detail that you require. (A single query won't produce per-group summary values and a list of each group's individual rows.)

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

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 rows 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 () 

An alternate implementation performs summary calculations in the program. By doing this, 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                 """) # fetch rows into data structure because we # must iterate through them multiple times 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 to show 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: 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 

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




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