15th April 2019: PostgreSQL Code of Conduct Committee 2018 Annual Report

Unsupported versions:
7.1

Like most other relational database products, PostgreSQL supports aggregate functions. An
aggregate function computes a single result from multiple input
rows. For example, there are aggregates to compute the `count`, `sum`, `avg` (average), `max`
(maximum) and `min` (minimum) over a set
of rows.

It is important to understand the interaction between
aggregates and SQL's **WHERE** and **HAVING** clauses. The fundamental difference between
**WHERE** and **HAVING** is
this: **WHERE** selects input rows before
groups and aggregates are computed (thus, it controls which rows
go into the aggregate computation), whereas **HAVING** selects group rows after groups and
aggregates are computed. Thus, the **WHERE**
clause may not contain aggregate functions; it makes no sense to
try to use an aggregate to determine which rows will be inputs to
the aggregates. On the other hand, **HAVING**
clauses always contain aggregate functions. (Strictly speaking,
you are allowed to write a **HAVING** clause
that doesn't use aggregates, but it's wasteful; the same
condition could be used more efficiently at the **WHERE** stage.)

As an example, we can find the highest low-temperature reading anywhere with

SELECT max(temp_lo) FROM weather;If we want to know what city (or cities) that reading occurred in, we might try

SELECT city FROM weather WHERE temp_lo = max(temp_lo);but this will not work since the aggregate

SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);This is OK because the sub-select is an independent computation that computes its own aggregate separately from what's happening in the outer select.

Aggregates are also very useful in combination with **GROUP BY** clauses. For example, we can get the
maximum low temperature observed in each city with

SELECT city, max(temp_lo) FROM weather GROUP BY city;which gives us one output row per city. We can filter these grouped rows using

SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING min(temp_lo) < 0;which gives us the same results for only the cities that have some below-zero readings. Finally, if we only care about cities whose names begin with "

SELECT city, max(temp_lo) FROM weather WHERE city like 'P%' GROUP BY city HAVING min(temp_lo) < 0;Note that we can apply the city-name restriction in