SQL Run

Database Programming

« Previous

Home

Contents

» Next

6. Summarizing and Grouping Data

The preceding chapter described scalar functions, which operate on individual row values. This chapter introduces SQL’s aggregate functions, or set functions, which operate on a group of values to produce a single, summarizing value. You apply an aggregate to a set of rows, which can be:

• All the rows in a table
• Only those rows specified by a WHERE clause
• Those rows created by a GROUP BY clause

A GROUP BY clause, which groups rows, often is used with a HAVING clause, which filters groups. No matter how many rows the input set contains, an aggregate function returns a single statistic: a sum, minimum, or average, for example.

The main difference between queries with and without aggregate functions is that nonaggregate queries process the rows one by one. Each row is processed independently and put into the result. (ORDER BY and DISTINCT make the DBMS look at all the rows, but they’re essentially postprocessing operations.) Aggregate queries do something completely different: they take a table as a whole and construct new rows from it.

Using Aggregate Functions

Table 6.1 lists SQL’s standard aggregate functions.

The important characteristics of the aggregate functions are:

• In Table 6.1, the expression expr often is a column name, but it also can be a literal, function, or any combination of chained or nested column names, literals, and functions.
• SUM() and AVG() work with only numeric data types. MIN() and MAX() work with character, numeric, and datetime data types. COUNT(expr) and COUNT(*) work with all data types.
• All aggregate functions except COUNT(*) ignore nulls. (You can use COALESCE() in an aggregate function argument to substitute a value for a null; see “Checking for Nulls with COALESCE()” in Chapter 5.)
• COUNT(expr) and COUNT(*) never return null but return either a positive integer or zero. The other aggregate functions return null if the set contains no rows or contains rows with only nulls.
• Default column headings for aggregate expressions vary by DBMS; use AS to name the result column. See “Creating Column Aliases with AS” in Chapter 4.

Tips for Aggregate Functions

• DBMSs provide additional aggregate functions to calculate other statistics, such as the standard deviation; search your DBMS documentation for aggregate functions or group functions.

Creating Aggregate Expressions

Aggregate functions can be tricky to use. This section explains what’s legal and what’s not.

• An aggregate expression can’t appear in a WHERE clause. If you want to find the title of the book with the highest sales, then you can’t use:

  SELECT title_id
  FROM titles
  WHERE sales = MAX(sales);  --Illegal

• You can’t mix nonaggregate (row-by-row) and aggregate expressions in a SELECT clause. A SELECT clause must contain either all nonaggregate expressions or all aggregate expressions. If you want to find the title of the book with the highest sales, then you can’t use:

  SELECT title_id, MAX(sales)
  FROM titles;              --Illegal

  The one exception to this rule is that you can mix nonaggregate and aggregate expressions for grouping columns (see “Grouping Rows with GROUP BY” later in this chapter):

  SELECT type, SUM(sales)
  FROM titles
  GROUP BY type;              --Legal

• You can use more than one aggregate expression in a SELECT clause:

  SELECT MIN(sales), MAX(sales)
  FROM titles;                --Legal

• You can’t nest aggregate functions:

  SELECT SUM(AVG(sales))
  FROM titles;              --Illegal

• You can use aggregate expressions in subqueries (see Chapter 8). This statement finds the title of the book with the highest sales:

  SELECT title_id, price        --Legal
  FROM titles
  WHERE sales = (SELECT MAX(sales) FROM titles);

• You can’t use subqueries in aggregate expressions:

  AVG(SELECT price FROM titles)   --Illegal

Tips for Aggregate Expressions

• Oracle lets you nest aggregate expressions in GROUP BY queries. The following example calculates the average of the maximum sales of all book types. Oracle evaluates the inner aggregate MAX(sales) for the grouping column type and then aggregates the results again:

  SELECT AVG(MAX(sales))
  FROM titles
  GROUP BY type;   --Legal in Oracle

  To replicate this query in standard SQL, use a subquery in the FROM clause:

  SELECT AVG(s.max_sales)
  FROM (SELECT MAX(sales) AS max_sales
          FROM titles
          GROUP BY type) s;

Finding a Minimum with MIN()

Use the aggregate function MIN() to find the minimum of a set of values.

To find the minimum of a set of values:

• Type:

  MIN(expr)

  expr is a column name, literal, or expression. The result has the same data type as expr.

Listing 6.1 and Figure 6.1 show some queries that involve MIN(). The first query returns the price of the lowest-priced book. The second query returns the earliest publication date. The third query returns the number of pages in the shortest history book.

Tips for MIN

• MIN() works with character, numeric, and datetime data types.
• With character data columns, MIN() finds the value that is lowest in the sort sequence; see “Sorting Rows with ORDER BY” in Chapter 4.
• DISTINCT isn’t meaningful with MIN(); see “Aggregating Distinct Values with DISTINCT” later in this chapter.

• String comparisons are case insensitive or case sensitive, depending on your DBMS; see the DBMS tip in “Tips for WHERE” in Chapter 4.

  When comparing two VARCHAR strings for equality, your DBMS might right-pad the shorter string with spaces and compare the strings position by position. In this case, the strings 'John' and 'John ' are equal. Refer to your DBMS documentation (or experiment) to determine which string MIN() returns.

Finding a Maximum with MAX()

Use the aggregate function MAX() to find the maximum of a set of values.

To find the maximum of a set of values:

• Type:

  MAX(expr)

  expr is a column name, literal, or expression. The result has the same data type as expr.

Listing 6.2 and Figure 6.2 show some queries that involve MAX(). The first query returns the author’s last name that is last alphabetically. The second query returns the prices of the cheapest and most expensive books, as well as the price range. The third query returns the highest revenue (= price × sales) among the history books.

Tips for MAX

• MAX() works with character, numeric, and datetime data types.
• With character data columns, MAX() finds the value that is highest in the sort sequence; see “Sorting Rows with ORDER BY” in Chapter 4.
• DISTINCT isn’t meaningful with MAX(); see “Aggregating Distinct Values with DISTINCT” later in this chapter.

• String comparisons are case insensitive or case sensitive, depending on your DBMS; see the DBMS tip in “Tips for WHERE” in Chapter 4.

  When comparing two VARCHAR strings for equality, your DBMS might right-pad the shorter string with spaces and compare the strings position by position. In this case, the strings 'John' and 'John ' are equal. Refer to your DBMS documentation (or experiment) to determine which string MAX() returns.

Calculating a Sum with SUM()

Use the aggregate function SUM() to find the sum (total) of a set of values.

To calculate the sum of a set of values:

• Type:

  SUM(expr)

  expr is a column name, literal, or numeric expression. The result’s data type is at least as precise as the most precise data type used in expr.

Listing 6.3 and Figure 6.3 show some queries that involve SUM(). The first query returns the total advances paid to all authors. The second query returns the total sales of books published in 2000. The third query returns the total price, sales, and revenue (= price × sales) of all books. Note a mathematical rule in action here: “The sum of the products doesn’t (necessarily) equal the product of the sums.”

Tips for SUM

• SUM() works with only numeric data types.
• The sum of no rows is null—not zero, as you might expect.

• In Microsoft Access date literals, omit the DATE keyword and surround the literal with # characters instead of quotes. To run Listing 6.3, change the date literals in the second query to #2000-01-01# and #2000-12-31#.

  In Microsoft SQL Server and Db2 date literals, omit the DATE keyword. To run Listing 6.3, change the date literals to '2000-01-01' and '2000-12-31'.

Calculating an Average with AVG()

Use the aggregate function AVG() to find the average, or arithmetic mean, of a set of values. The arithmetic mean is the sum of a set of quantities divided by the number of quantities in the set.

To calculate the average of a set of values:

• Type:

  AVG(expr)

  expr is a column name, literal, or numeric expression. The result’s data type is at least as precise as the most precise data type used in expr.

Listing 6.4 and Figure 6.4 shows some queries that involve AVG(). The first query returns the average price of all books if prices were doubled. The second query returns the average and total sales for business books; both calculations are null (not zero) because the table contains no business books. The third query uses a subquery (see Chapter 8) to list the books with above-average sales.

Tips for AVG

• AVG() works with only numeric data types.
• The average of no rows is null—not zero, as you might expect.
• If you’ve used, say, 0 or −1 instead of null to represent missing values, then the inclusion of those numbers in AVG() calculations yields an incorrect result. Use NULLIF() to convert the missing-value numbers to nulls so they’ll be excluded from calculations; see “Comparing Expressions with NULLIF()” in Chapter 5.
• MySQL 4.0 and earlier lack subquery support and won’t run the third query in Listing 6.4.

Counting Rows with COUNT()

Use the aggregate function COUNT() to count the number of rows in a set of values. COUNT() has two forms:

• COUNT(expr) returns the number of rows in which expr is not null.
• COUNT(*) returns the count of all rows in a set, including nulls and duplicates.

To count non-null rows:

• Type:

  COUNT(expr)

  expr is a column name, literal, or expression. The result is an integer greater than or equal to zero.

To count all rows, including nulls:

• Type:

  COUNT(*)

  The result is an integer greater than or equal to zero.

Listing 6.5 and Figure 6.5 show some queries that involve COUNT(expr) and COUNT(*). The three queries count rows in the table titles and are identical except for the WHERE clause. The row counts in the first query differ because the column price contains a null. In the second query, the row counts are identical because the WHERE clause eliminates the row with the null price before the count. The third query shows the row-count differences between the results of the first two queries.

Tips for COUNT

• COUNT(expr) and COUNT(*) work with all data types and never return null.
• DISTINCT isn’t meaningful with COUNT(*); see “Aggregating Distinct Values with DISTINCT” later in this chapter.
• COUNT(*) - COUNT(expr) returns the number of nulls, and ((COUNT(*) - COUNT(expr))*100)/COUNT(*) returns the percentage of nulls.

Aggregating Distinct Values with DISTINCT

You can use DISTINCT to eliminate duplicate values in aggregate function calculations; see “Eliminating Duplicate Rows with DISTINCT” in Chapter 4. The general syntax of an aggregate function is:

agg_func([ALL | DISTINCT] expr)

agg_func is MIN, MAX, SUM, AVG, or COUNT. expr is a column name, literal, or expression. ALL applies the aggregate function to all values, and DISTINCT specifies that each unique value is considered. ALL is the default and is usually omitted in practice.

With SUM(), AVG(), and COUNT(expr), DISTINCT eliminates duplicate values before the sum, average, or count is calculated. DISTINCT isn’t meaningful with MIN() and MAX(); you can use it, but it won’t change the result. You can’t use DISTINCT with COUNT(*).

To calculate the sum of a set of distinct values:

• Type:

  SUM(DISTINCT expr)

  expr is a column name, literal, or numeric expression. The result’s data type is at least as precise as the most precise data type used in expr.

To calculate the average of a set of distinct values:

• Type:

  AVG(DISTINCT expr)

  expr is a column name, literal, or numeric expression. The result’s data type is at least as precise as the most precise data type used in expr.

To count distinct non-null rows:

• Type:

  COUNT(DISTINCT expr)

  expr is a column name, literal, or expression. The result is an integer greater than or equal to zero.

The queries in Listing 6.6 return the count, sum, and average of book prices. The non-DISTINCT and DISTINCT results in Figure 6.6 differ because the DISTINCT results eliminate the duplicates of prices $12.99 and $19.95 from calculations.

DISTINCT in a SELECT clause (Chapter 4) and DISTINCT in an aggregate function don’t return the same result.

The three queries in Listing 6.7 count the author IDs in the table title_authors. Figure 6.7 shows the results. The first query counts all the author IDs in the table. The second query returns the same result as the first query because COUNT() already has done its work and returned a value in a single row before DISTINCT is applied. In the third query, DISTINCT is applied to the author IDs before COUNT() starts counting.

Mixing non-DISTINCT and DISTINCT aggregates in the same SELECT clause can produce misleading results.

The four queries in Listing 6.8 show the four combinations of non-DISTINCT and DISTINCT sums and counts. Of the four results in Figure 6.8, only the first result (no DISTINCTs) and final result (all DISTINCTs) are consistent mathematically, which you can verify with AVG(price) and AVG(DISTINCT price). In the second and third queries (mixed non-DISTINCTs and DISTINCTs), you can’t calculate a valid average by dividing the sum by the count.

Tips for DISTINCT

• The ratio COUNT(DISTINCT)/COUNT() tells you how repetitive a set of values is. A ratio of one or close to it means that the set contains many unique values. The closer the ratio is to zero, the more repeats the set has.

• Microsoft Access doesn’t support DISTINCT aggregate functions. This statement, for example, is illegal in Access:

  SELECT SUM(DISTINCT price)
  FROM titles;  --Illegal in Access

  But you can replicate it with this subquery (see “Using Subqueries as Column Expressions” in Chapter 8):

  SELECT SUM(price)
  FROM (SELECT DISTINCT price
          FROM titles);

  This Access workaround won’t let you mix non-DISTINCT and DISTINCT aggregates, however, as in the second and third queries in Listing 6.8.

  MySQL 4.1 and earlier support COUNT(DISTINCT expr) but not SUM(DISTINCT expr) and AVG(DISTINCT expr) and so won’t run Listings 6.6 and 6.8. MySQL 5.0 and later support all DISTINCT aggregates.

Grouping Rows with GROUP BY

To this point, I’ve used aggregate functions to summarize all the values in a column or just those values that matched a WHERE search condition. You can use the GROUP BY clause to divide a table into logical groups (categories) and calculate aggregate statistics for each group.

An example will clarify the concept. Listing 6.9 uses GROUP BY to count the number of books that each author wrote (or cowrote). In the SELECT clause, the column au_id identifies each author, and the derived column num_books counts each author’s books. The GROUP BY clause causes num_books to be calculated for every unique au_id instead of only once for the entire table. Figure 6.9 shows the result. In this example, au_id is called the grouping column.

The GROUP BY clause’s important characteristics are:

• The GROUP BY clause comes after the WHERE clause and before the ORDER BY clause.
• Grouping columns can be column names or derived columns.

• No columns from the input table can appear in an aggregate query’s SELECT clause unless they’re also included in the GROUP BY clause. A column has (or can have) different values in different rows, so there’s no way to decide which of these values to include in the result if you’re generating a single new row from the table as a whole. The following statement is illegal because GROUP BY returns only one row for each value of type; the query can’t return the multiple values of pub_id that are associated with each value of type:

  SELECT type, pub_id, COUNT(*)
  FROM titles
  GROUP BY type;          --Illegal

• If the SELECT clause contains a complex nonaggregate expression (more than just a simple column name), then the GROUP BY expression must match the SELECT expression exactly.
• To nest groups, specify multiple, comma-separated grouping columns in the GROUP BY clause. Data are summarized at the final specified group.
• If a grouping column contains a null, then that row becomes a group in the result. If a grouping column contains more than one null, then the nulls are put into a single group. A group that contains multiple nulls doesn’t imply that the nulls equal one another.
• To eliminate rows before grouping occurs, use a WHERE clause in a query containing a GROUP BY clause.
• You can’t use a column alias in the GROUP BY clause, although table aliases are allowed as qualifiers; see “Creating Table Aliases with AS” in Chapter 7.
• Without an ORDER BY clause, groups returned by GROUP BY aren’t in any particular order. To sort the result of Listing 6.9 by the descending number of books, for example, add the clause ORDER BY "num_books" DESC.

To group rows:

• Type:

  SELECT columns
  FROM table
  [WHERE search_condition]
  GROUP BY grouping_columns
  [HAVING search_condition]
  [ORDER BY sort_columns];

  columns and grouping_columns are one or more comma-separated column names, and table is the name of the table that contains columns and grouping_columns. The nonaggregate columns that appear in columns also must appear in grouping_columns. The order of the column names in grouping_columns determines the grouping levels, from the highest to the lowest level of grouping.

  The GROUP BY clause restricts the rows of the result; only one row appears for each distinct value in the grouping column or columns. Each row in the result contains summary data related to the specific value in its grouping columns.

  If the statement includes a WHERE clause, then the DBMS groups values after it applies search_condition to the rows in table. If the statement includes an ORDER BY clause, then the columns in sort_columns must be drawn from those in columns. The WHERE and ORDER BY clauses are covered in “Filtering Rows with WHERE” and “Sorting Rows with ORDER BY” in Chapter 4. HAVING, which filters grouped rows, is covered in the next section.

Listing 6.10 and Figure 6.10 show the difference between COUNT(expr) and COUNT(*) in a query that contains GROUP BY. The table publishers contains one null in the column state (for publisher P03 in Germany). Recall from “Counting Rows with COUNT()” earlier in this chapter that COUNT(expr) counts non-null values and COUNT(*) counts all values, including nulls. In the result, GROUP BY recognizes the null and creates a null group for it. COUNT(*) finds (and counts) the one null in the column state. But COUNT(state) contains a zero for the null group because COUNT(state) finds only a null in the null group, which it excludes from the count—that’s why you have the zero.

If a nonaggregate column contains nulls, then using COUNT(*) rather than COUNT(expr) can produce misleading results. Listing 6.11 and Figure 6.11 show summary sales statistics for each type of book. The sales value for one of the biographies is null, so COUNT(sales) and COUNT(*) differ by 1. The average calculation in the fifth column, SUM/COUNT(sales), is consistent mathematically, whereas the sixth-column average, SUM/COUNT(*), is not. I’ve verified the inconsistency with AVG(sales) in the final column. (Recall a similar situation in Listing 6.8 in “Aggregating Distinct Values with DISTINCT” earlier in this chapter.)

Listing 6.12 and Figure 6.12 show a simple GROUP BY query that calculates the total sales, average sales, and number of titles for each type of book. In Listing 6.13 and Figure 6.13, I’ve added a WHERE clause to eliminate books priced less than $13 before grouping. I’ve also added an ORDER BY clause to sort the result by descending total sales of each book type.

Listing 6.14 and Figure 6.14 use multiple grouping columns to count the number of titles of each type that each publisher publishes.

In Listing 6.15 and Figure 6.15, I revisit Listing 5.31 in “Evaluating Conditional Values with CASE” in Chapter 5. But instead of listing each book categorized by its sales range, I use GROUP BY to list the number of books in each sales range.

When used without an aggregate function, GROUP BY acts like DISTINCT (Listing 6.16 and Figure 6.16). For information about DISTINCT, see “Eliminating Duplicate Rows with DISTINCT” in Chapter 4.

You can use GROUP BY to look for patterns in your data. In Listing 6.17 and Figure 6.17, I’m looking for a relationship between price categories and average sales.

Tips for GROUP BY

• Use the WHERE clause to exclude rows that you don’t want grouped and use the HAVING clause to filter rows after they have been grouped. The next section covers HAVING.
• Don’t rely on GROUP BY to sort your result. Include ORDER BY whenever you use GROUP BY (even though I’ve omitted ORDER BY in some examples). In some DBMSs, a GROUP BY implies an ORDER BY.
• The multiple values returned by an aggregate function in a GROUP BY query are called vector aggregates. In a query that lacks a GROUP BY clause, the single value returned by an aggregate function is a scalar aggregate.
• You should create indexes for columns that you group frequently (see Chapter 12).

• In Microsoft Access, use the Switch() function instead of the CASE expression in Listing 6.15. See the DBMS tip in “Tips for CASE” in Chapter 5.

  MySQL 4.1 and earlier don’t allow CASE in a GROUP BY clause and so won’t run Listing 6.15. MySQL 5.0 and later will run it.

Filtering Groups with HAVING

The HAVING clause sets conditions on the GROUP BY clause similar to the way that WHERE interacts with SELECT. The HAVING clause’s important characteristics are:

• The HAVING clause comes after the GROUP BY clause and before the ORDER BY clause.
• Just as WHERE limits the number of rows displayed by SELECT, HAVING limits the number of groups displayed by GROUP BY.
• The WHERE search condition is applied before grouping occurs, and the HAVING search condition is applied after.
• HAVING syntax is similar to the WHERE syntax, except that HAVING can contain aggregate functions.
• A HAVING clause can reference any of the items that appear in the SELECT list.

The sequence in which the WHERE, GROUP BY, and HAVING clauses are applied is:

1. The WHERE clause filters the rows that result from the operations specified in the FROM and JOIN clauses.
2. The GROUP BY clause groups the output of the WHERE clause.
3. The HAVING clause filters rows from the grouped result.

To filter groups:

• Following the GROUP BY clause, type:

  HAVING search_condition

  search_condition is a search condition used to filter groups. search_condition can contain aggregate functions but otherwise is identical to the WHERE search condition, described in “Filtering Rows with WHERE” and subsequent sections in Chapter 4. You can combine and negate multiple HAVING conditions with the logical operators AND, OR, and NOT.

  The HAVING search condition is applied to the rows in the output produced by grouping. Only the groups that meet the search condition appear in the result. You can apply a HAVING clause only to columns that appear in the GROUP BY clause or in an aggregate function.

In Listing 6.18 and Figure 6.18, I revisit Listing 6.9 earlier in this chapter, but instead of listing the number of books that each author wrote (or cowrote), I use HAVING to list only the authors who have written three or more books.

In Listing 6.19 and Figure 6.19, the HAVING condition also is an aggregate expression in the SELECT clause. This query still works if you remove the AVG() expression from the SELECT list (Listing 6.20 and Figure 6.20).

In Listing 6.21 and Figure 6.21, multiple grouping columns count the number of titles of each type that each publisher publishes. The HAVING condition removes groups in which the publisher has one or fewer titles of a particular type. This query retrieves a subset of the result of Listing 6.14 earlier in this chapter.

In Listing 6.22 and Figure 6.22, the WHERE clause first removes all rows except for books from only publishers P03 and P04. Then the GROUP BY clause groups the output of the WHERE clause by type. Finally, the HAVING clause filters rows from the grouped result.

Tips for HAVING

• Generally, a HAVING clause should involve only aggregates. The only conditions that you specify in the HAVING clause are those conditions that must be applied after the grouping operation has been performed. It’s more efficient to specify conditions that can be applied before the grouping operation in the WHERE clause. The following statements, for example, are equivalent, but the first statement is preferable because it reduces the number of rows that have to be grouped:

  --Faster
SELECT pub_id, SUM(sales)
  FROM titles
  WHERE pub_id IN ('P03', 'P04')
  GROUP BY pub_id
  HAVING SUM(sales) > 10000;

--Slower
SELECT pub_id, SUM(sales)
  FROM titles
  GROUP BY pub_id
  HAVING SUM(sales) > 10000
    AND pub_id IN ('P03', 'P04');