Chapter 8. Generating Summaries

Chapter 8. Generating Summaries

Section 8.0.  Introduction

Recipe 8.1.  Summarizing with COUNT⁠(⁠ ⁠ ⁠)

Recipe 8.2.  Summarizing with MIN⁠(⁠ ⁠ ⁠) and MAX⁠(⁠ ⁠ ⁠)

Recipe 8.3.  Summarizing with SUM⁠(⁠ ⁠ ⁠) and AVG⁠(⁠ ⁠ ⁠)

Recipe 8.4.  Using DISTINCT to Eliminate Duplicates

Recipe 8.5.  Finding Values Associated with Minimum and Maximum Values

Recipe 8.6.  Controlling String Case Sensitivity for MIN⁠(⁠ ⁠ ⁠) and MAX⁠(⁠ ⁠ ⁠)

Recipe 8.7.  Dividing a Summary into Subgroups

Recipe 8.8.  Summaries and NULL Values

Recipe 8.9.  Selecting Only Groups with Certain Characteristics

Recipe 8.10.  Using Counts to Determine Whether Values Are Unique

Recipe 8.11.  Grouping by Expression Results

Recipe 8.12.  Categorizing Noncategorical Data

Recipe 8.13.  Controlling Summary Display Order

Recipe 8.14.  Finding Smallest or Largest Summary Values

Recipe 8.15.  Date-Based Summaries

Recipe 8.16.  Working with Per- Group and Overall Summary Values Simultaneously

Recipe 8.17.  Generating a Report That Includes a Summary and a List

8.0. Introduction

Database systems are useful for storing and retrieving records, but they can also summarize your data in more concise forms. 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 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 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 recordssales 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, 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 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 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 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 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 many distinct values there are, 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 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 demonstrates 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 driver_log and mail tables. These were also used heavily in Chapter 7, so they should look familiar. A third table used recurrently throughout the chapter is states , which has rows containing a few columns of information for each of the United States:

mysql>

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 
...

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 July, 2004, as reported 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 tables directory of the recipes distribution. Section 5.15 describes the kjv table.