Database systems are useful for storing and retrieving records, but they can also summarize your data in more
forms. Summaries are useful when you want the overall picture rather than the details. They're also typically more readily
than a long list of records. Summary techniques enable 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
useful if you have to add up the order amounts yourself. Let MySQL do it.
The examples just mentioned
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
or category for counting. Such summaries are
histograms, where you
items into a set of
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 recordssales totals are computed from sales values in individual order records.
Yet another kind of summary produces
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
values are present. If you want to know the states in which you have customers, you want a list of the distinct state
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, and then count them.
The type of summaries that you generate 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
, 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 namesthat 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
. These are so called because they
on aggregates (groups) of values. Aggregate functions include
, which counts rows or values in a query result;
, which find smallest and largest values; and
, which produce sums and means of values. These functions can be used to compute a value for the entire result set, or with a
clause to group the rows into
and obtain an aggregate value for each one.
To obtain a list of unique values, use
To count how many distinct values there are, use
The recipes in this chapter first illustrate basic summary techniques, and then show how to perform more complex summary operations. You'll find additional examples of summary
in later chapters, particularly those that cover joins and statistical operations. (See Chapters Chapter 12 and Chapter 13.)
Summary queries sometimes involve complex expressions. For summaries that you execute often, keep in mind that views can make queries easier to use. Section 3.12
the basic technique of creating a view. Section 8.1 shows how it applies to summary simplification, and you'll see easily how it can be used in later sections of the chapter as well.
The primary tables used for examples in this chapter are the
tables. These were also used heavily in Chapter 7, so they should look familiar. A third table used recurrently throughout the chapter is
, which has rows containing a few
of information for each of the United States:
SELECT * FROM states ORDER BY name;
name abbrev statehood pop
Alabama AL 1819-12-14 4530182
Alaska AK 1959-01-03 655435
Arizona AZ 1912-02-14 5743834
Arkansas AR 1836-06-15 2752629
California CA 1850-09-09 35893799
Colorado CO 1876-08-01 4601403
Connecticut CT 1788-01-09 3503604
columns list the full state name and the corresponding abbreviation. The
column indicates the day on which the state entered the Union.
is the state population as of July, 2004, as
by the U.S. Census Bureau.
This chapter uses other tables occasionally as well. You can create most of them with the scripts found in the
directory of the
distribution. Section 5.15 describes the