Generating Summaries - MySQL - page 272

Summary operations in MySQL involve the following SQL constructs:
• To compute a summary value from a set of individual values, use one of the func‐
tions known as aggregate functions. These are so called because they operate on
aggregates (groups) of values. Aggregate functions include COUNT() , which counts
rows 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 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 unique values, use COUNT(DISTINCT) rather than COUNT() .
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 methods in later chapters, particularly those that cover joins and statistical
operations. (See Chapter 14 and Chapter 15 .)
Summary queries sometimes involve complex expressions. For summaries that you
execute often, keep in mind that views can make queries easier to use. Recipe 3.7 dem‐
onstrates the basic technique of creating a view. Recipe 8.2 shows how it applies to
summary simplification, and you'll easily see how it can be used in later sections of the
chapter as well.
The primary tables used for examples in this chapter are the driver_log and mail tables.
These were also used in Chapter 7 , so they should look familiar. A third table used
throughout the chapter is states , which has rows containing a few columns of infor‐
mation for each of the United States:
mysql> SELECT * FROM states ORDER BY name;
+----------------+--------+------------+----------+
| name | abbrev | statehood | pop |
+----------------+--------+------------+----------+
| Alabama | AL | 1819-12-14 | 4779736 |
| Alaska | AK | 1959-01-03 | 710231 |
| Arizona | AZ | 1912-02-14 | 6392017 |
| Arkansas | AR | 1836-06-15 | 2915918 |
| California | CA | 1850-09-09 | 37253956 |
| Colorado | CO | 1876-08-01 | 5029196 |
| Connecticut | CT | 1788-01-09 | 3574097 |
…
The name and abbrev columns list the full state name and the corresponding abbrevi‐
ation. The statehood column indicates the day on which the state entered the Union.
pop is the state population from the 2010 census, as reported by the US Census Bureau.