GROUP BY, HAVING and Aggregate Functions

Aggregate functions reduce many rows to a single value, and lets you do that once per group. Together they answer questions such as "how many orders per customer?" or "what is the average salary per department?".

The examples use the table from the practice database, with the columns , , , and .

The core aggregates

Function	Returns
	number of rows
	number of rows where the column is not NULL
	number of different non-NULL values
	total
	average of the non-NULL values
/	smallest / largest value

All of them except ignore . For values , returns 15, not 10, because the row is skipped rather than counted as zero.

GROUP BY

Each distinct becomes one output row, and employees whose is form one group of their own. The rule to remember: every column in must either be inside an aggregate function or be listed in . Selecting here would be an error, because a department has many names and SQL cannot pick one.

You can group by several columns, and each distinct combination becomes a group:

WHERE filters rows, HAVING filters groups

Queries are logically evaluated in this order, which is not the order you write them:

1. and
2. (filters individual rows)
4. (filters groups)

Because runs before grouping, it cannot use an aggregate. Use for conditions on aggregates:

This ignores rows with no salary, keeps only departments with at least five people, and sorts the survivors. Put every condition you can in : it removes rows early, which is faster than grouping them first.

Conditional aggregation

A expression inside an aggregate counts or sums only some rows. It is the portable way to turn rows into columns (a "pivot"), and it works in every database:

Common mistakes

• Writing . Use .
• Assuming counts all rows. It skips s, which is often what you want but sometimes a surprise.
• Using on a column where should mean zero. Wrap it: .
• Selecting a column that is neither grouped nor aggregated.