14th February 2019: PostgreSQL 11.2, 10.7, 9.6.12, 9.5.16, and 9.4.21 Released!

Unsupported versions:
7.0

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
instances.

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 which 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