ProblemYou want to create a report that displays a summary, together with the list of rows associated with each summary value. SolutionUse 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. DiscussionSuppose 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:
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:
Such problems can be solved using a couple of approaches:
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. |