MySQL Cookbook

7.1 Introduction

Database systems are useful for storing and retrieving records, but they also can boil down information to summarize your data in more concise form. Summaries are useful when you want the overall picture rather than the details. They're also typically more readily understood than a long list of records. Summary techniques allow you to answer questions such as "How many?" or "What is the total?" or "What is the range of values?" If you're running a business, you may want to know how many customers you have in each state, or how much sales volume you're generating each month. You could determine the per-state count by producing a list of customer records and counting them yourself, but that makes no sense when MySQL can count them for you. Similarly, to determine sales volume by month, a list of raw order information records is not especially useful if you have to add up the order amounts yourself. Let MySQL do it.

The examples just mentioned illustrate two common summary types. The first (the number of customer records per state) is a counting summary. The content of each record is important only for purposes of placing it into the proper group or category for counting. Such summaries are essentially histograms, where you sort items into a set of bins and count the number of items in each bin. The second example (sales volume per month) is an instance of a summary that's based on the contents of records—sales totals are computed from sales values in individual order records.

Yet another kind of summary produces neither counts nor sums, but simply a list of unique values. This is useful if you don't care how many instances of each value are present, but only which values are present. If you want to know the states in which you have customers, you want a list of the distinct state names contained in the records, not a list consisting of the state value from every record. Sometimes it's even useful to apply one summary technique to the result of another summary. For example, to determine how many states your customers live in, generate a list of unique customer states, then count them.

The type of summaries you can perform may depend on the kind of data you're working with. A counting summary can be generated from any kind of values, whether they be numbers, strings, or dates. For summaries that involve sums or averages, only numeric values can be used. You can count instances of customer state names to produce a demographic analysis of your customer base, but you cannot add or average state names—that doesn't make sense.

Summary operations in MySQL involve the following SQL constructs:

• To compute a summary value from a set of individual values, use one of the functions known as aggregate functions. These are so called because they operate on aggregates (groups) of values. Aggregate functions include COUNT( ), which counts records or values in a query result; MIN( ) and MAX( ), which find smallest and largest values; and SUM( ) and AVG( ), which produce sums and means of values. These functions can be used to compute a value for the entire result set, or with a GROUP BY clause to group the rows into subsets and obtain an aggregate value for each one.

• To obtain a list of unique values, use SELECT DISTINCT rather than SELECT.

• To count how may distinct values there are, use COUNT(DISTINCT) rather than COUNT( ).

The recipes in this chapter first illustrate basic summary techniques, then show how to perform more complex summary operations. You'll find additional examples of summary methods in later chapters, particularly those that cover joins and statistical operations. (See Chapter 12 and Chapter 13.)

The primary tables used for examples here are the driver_log and mail tables. These were also used heavily in Chapter 6, so they should look familiar. A third table used recurrently throughout the chapter is states, which has rows containing a few pieces of information for each of the United States:

mysql> SELECT * FROM states ORDER BY name;
+----------------+--------+------------+----------+
| name           | abbrev | statehood  | pop      |
+----------------+--------+------------+----------+
| Alabama        | AL     | 1819-12-14 |  4040587 |
| Alaska         | AK     | 1959-01-03 |   550043 |
| Arizona        | AZ     | 1912-02-14 |  3665228 |
| Arkansas       | AR     | 1836-06-15 |  2350725 |
| California     | CA     | 1850-09-09 | 29760021 |
| Colorado       | CO     | 1876-08-01 |  3294394 |
| Connecticut    | CT     | 1788-01-09 |  3287116 |
...

The name and abbrev columns list the full state name and the corresponding abbreviation. The statehood column indicates the day on which the state entered the Union. pop is the state population as of April, 1990, as reported by the U.S. Census Bureau.

Other tables are used occasionally as well. You can create most of them with the scripts found in the tables directory of the recipes distribution. The tables containing data from the baseball1.com baseball database can be created using the instructions in the baseball1 directory, and the kjv table is described in Recipe 4.12.