Week 1

Day 5
Aggregates

Today is all about SQL aggregates. Aggregates are functions that take information stored in a single column and report a single result. Today’s lesson covers the following points:

•  What aggregates are available

•  A simple example for each aggregate

•  How aggregates are affected by null values

•  Aggregates with a WHERE clause

•  Expressions inside aggregates

•  Using the ISNULL() function

Introduction to Aggregates

Let’s start out by defining what an aggregate really is.

An aggregate is a SQL Server function that returns a single value for a set of rows in a column.

Five aggregates are defined by T-SQL. Table 5.1 lists the five functions and what they do.

Notice that aggregates do not operate on null values. When null values are included in the aggregation, that row is effectively left out of the calculation. See the next main section, titled “Aggregates with Null,” for a discussion on this topic.

You can take two paths now; if you think you get the aggregate functions, you should try a few on your own or skip to the exercises at the end of the chapter. Then, pick up today’s topics again at “Using Count,” because that aggregate has a few exceptions. If you’d like to see some examples of the aggregates, though, I’ll demonstrate each of them operating on the price column in the titles table.

Using SUM

Let’s start with a simple example. To total up the prices of all the books—maybe somebody wanted to know what it would cost for “one of everything”—use this query:

      select     sum(price)
      from       titles

      ----------------
      236.26

      (1  row(s)  affected)

All the books that have a price—that is, all books where the price is not null—are added up and returned in response to this query.

Notice that the row count, appearing after the result set, shows that only one row was affected. Although the sum operated on 16 rows, which is the number of non-null rows in the titles table, where aggregates are concerned, the row count reports the number of rows that were returned.

One last thing I’d like to mention is the name of the sum column in the result set. It doesn’t have a name.

It wouldn’t really apply to call this column “price,” because what you got back is the total of that column. When any kind of calculation is performed on a column, including aggregate functions, the column name will be blank. If you ask for “price * 1,” you’ll get back a blank column heading. If you want to name the column, you’ll have to provide a column alias. Column aliasing was covered on the second day.

Using AVG

The AVG aggregate returns the average of the column values. If you want the average price of all the books in the titles table, you would use this query:

      select     avg(price)    “Average”
      from       titles

      Average
      ----------------------
      14.77

The average price for a book in the titles table is $14.77. Null priced books are not included in the calculation; they do not push the average price up or down.

Using MIN and MAX

You may request multiple aggregates in the same SELECT. In this example, you request the lowest-priced book and the highest-priced book in the titles table:

      select     min(price)   Cheapest,   max(price)   “Most  Expensive”
      from        titles

      Cheapest               Most Expensive
      ---------------------------------
      2.99                   22.95

      (1   row(s)   affected)

The minimum value in the price column is $2.99; the maximum, $22.95.

---

Technical Note:  If you ask for multiple aggregates in a single SELECT, the server won’t use an index to resolve the query. It may be faster to run two separate queries or to use a subquery, like this:

select       min(price)    Cheapest,
                  (select  max(price)  “Most  Expensive” from  titles)
from         titles

Subqueries are covered fully on Day 8.

---

Using COUNT

The COUNT aggregate is used to get the exact number of rows that match a set of conditions. So far, the examples haven’t used a WHERE clause with an aggregate. (I talk about that in the section “Aggregates with WHERE”.) COUNT can take three forms. It can be used with a column name, with an asterisk, or with the DISTINCT keyword.

COUNT with a Column Name

Here is the example I’ve been using with the other aggregates. For a count of the number of non-null values in the price column, use this query:

      select     count(price)
      from       titles

      --------------------
      16

There are 16 rows in the titles table that have a value in their price column.