SQL Pocket Guide, 3rd Edition

Grouping and Summarizing

SQL enables you to collect rows into groups and to summarize those groups in various ways, ultimately returning just one row per group. You do this using the GROUP BY and HAVING clauses, as well as various aggregate functions.

Aggregate Functions

An aggregate function takes a group of values, one from each row in a group of rows, and returns one value as output. One of the most common aggregate functions is COUNT, which counts non-null values in a column. For example, to count the number of waterfalls associated with a county, specify:

SELECT COUNT(u.county_id) AS county_count
FROM upfall u;

16

Add DISTINCT to the preceding query to count the number of counties containing waterfalls:

SELECT COUNT(DISTINCT u.county_id)
       AS county_count
FROM upfall u;

6

The ALL behavior is the default, counting all values: COUNT(expression) is equivalent to COUNT(ALL expression).

COUNT is a special case of an aggregate function because you can pass the asterisk (*) to count rows rather than column values:

SELECT COUNT(*) FROM upfall;

Nullity is irrelevant when COUNT(*) is used because the concept of null applies only to columns, not to rows as a whole. All other aggregate functions ignore nulls.

Table 1-6 lists some commonly available aggregate functions. However, most database vendors implement aggregate functions well beyond those shown.

Table 1-6. Common aggregate functions

Function	Description
AVG(x)	Returns the mean.
COUNT(x)	Counts non-null values.
MAX(x)	Returns the greatest value.
MEDIAN( ...