SQL Pocket Guide, 2nd 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;

14

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 aggregate functions 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 entire rows as a whole. All other aggregate functions ignore nulls.

Table 11 lists some commonly available aggregate functions. However, most database vendors implement aggregate functions beyond those shown. Check your documentation if you need an aggregation beyond those listed in Table 11.

Table 11. Common aggregate functions

Function	Description