Using Aggregate Functions

The following five primary aggregate functions are supported by most major databases:

• AVG calculates the average value of a numeric column.

• COUNT obtains the number of rows that match a specific condition.

• MAX obtains the greatest value in a numeric column.

• MIN obtains the smallest value in a numeric column.

• SUM obtains the total of all values in a numeric column.

You can use these functions in two locations within SELECT statements:

• The SELECT list (the list of columns being selected)

• The HAVING clause (discussed later in this chapter)

The following example retrieves the cost of the most expensive product in a products table:

 SELECT MAX(product_price) AS expensive FROM products 

Here, the MAX function is assigned to an alias using AS so that it can use the value.

The use of aliases within SELECT statements was discussed previously in Chapter 44, "Basic SQL."

Multiple aggregate functions can be used within a single SELECT statement. This next example retrieves both the most and least expensive items in a products table:

 SELECT MAX(product_price) AS expensive, MIN(product_price) AS cheap FROM products 

NOTE

Some databases extend the standard set of aggregate functions with additional proprietary functions. Examples are SQL Server's STDEV (statistical standard deviation) and VAR (statistical variance of all values) functions. If your database supports these functions, you can use them in the same way you use the standard functions. The only downside is that the SQL code is not portable to other databases.

Handling NULL Values

With the exception of COUNT, aggregate functions ignore NULL values. So returning the MIN of a column returns the actual value (and not NULL). COUNT obtains the number of rows that match a condition and as such can (optionally) include rows with NULL values. You determine whether or not to include NULL values by specifying (or not specifying) a column to count.

This first example counts the number of rows in a products table:

 SELECT COUNT(*) AS num_products FROM products 

This next example counts only the rows that have a product description (not NULL in the prod_desc column):

 SELECT COUNT(prod_desc) AS num_products FROM products 

As you can see, if * is used, all rows are counted. If a column name is specified, only rows that do not have a NULL value in that column are counted.

Another important distinction between COUNT and the other aggregate functions is the returned value. With the exception of COUNT, the aggregate functions return NULL if no rows are processed (for example, the SUM of no rows). COUNT, however, returns 0 if no rows are processed (or if no rows match selection criteria).

Processing Distinct Values

By default, the aggregate functions perform calculations on all rows. To calculate only unique values, you can use the DISTINCT keyword. This example counts the number of unique products in a table:

 SELECT COUNT(DISTINCT prod_name) AS num_prods FROM products 

TIP

The opposite of DISTINCT is ALL, which you can use when all rows are to be processed. But because that is the default behavior, if neither ALL nor DISTINCT is specified, most developers ignore that keyword.

NOTE

You can use DISTINCT with AVG, COUNT, and SUM, but not with MAX and MIN. Obviously, if you want to know the greatest or smallest values in a column, DISTINCT would have no relevance.