Database Reference
In-Depth Information
This scenario is a variation on the “different levels of summary information” problem
discussed in
Recipe 8.13
. 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 create 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 have multiple solutions:
• 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
:
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
:
if
cur_name
!=
name
:
# new driver; print driver's summary info
print
(
"Name:
%s
; days on road:
%d
; miles driven:
%d
"
%
