Counting and grouping

From a table to a number

Often you do not want rows at all. You want a number: how many, how high, how many in each group. That is what aggregates are for.

An aggregate turns many rows into one

An aggregate function computes a single result from many input rows. Count gives the number of rows. Max gives the highest value. This query returns one row: six visits, and a highest systolic pressure of one hundred and sixty three. The tutorial lists count, sum, average, max and min.

• SELECT count(), max(systolic)
• FROM visits;

GROUP BY gives one row per group

Add group by, and the aggregate is computed once for each group. Here, one row per clinic: the number of visits, and the highest systolic reading. North has three visits and a high of one fifty two. South has three visits and a high of one sixty three.

• SELECT clinic, count(),
• max(systolic)
• FROM visits
• GROUP BY clinic;

An aggregate cannot sit in WHERE

A common slip: asking for the patient whose reading equals the maximum, by putting max in the where clause. The database refuses. Why? The where clause decides which rows go into the aggregate, so it is evaluated first. The fix is a subquery: an independent calculation that finds the maximum on its own. Now the query works, and returns patient P04.

• SELECT patient
• FROM visits
• WHERE systolic = max(systolic);
• SELECT patient
• FROM visits
• WHERE systolic =
• (SELECT max(systolic) FROM visits);

WHERE first, then the aggregate

That slip points to a rule worth remembering. First, the where clause picks the rows. Second, the aggregate is computed over only those rows. Third, you get the result. Because where comes first, it cannot use an aggregate that has not been computed yet.

• Step 1 · WHERE picks rows · Which rows take part. · search
• Step 2 · The aggregate computes · Over only those rows. · chart
• Step 3 · The result · One row, or one per group. · check

Check yourself

Pause and think. Why can an aggregate such as max not be used in a where clause? The answer: where decides which rows enter the aggregate, so it is evaluated first.

• WHERE decides which rows enter the aggregate, so it is evaluated first
• Aggregates only work on text columns
• The database does not know the word max
• WHERE only accepts numbers

Keep these in mind

An aggregate computes one result from many rows. Count, sum, average, max and min are aggregates. Group by gives one result per group. And where runs first, so it cannot use an aggregate.

• An aggregate computes one result from many rows
• count, sum, avg, max and min are aggregates
• GROUP BY gives one result per group
• WHERE runs first, so it cannot use an aggregate