21st May 2020: PostgreSQL 13 Beta 1 Released!

Unsupported versions:
7.0

This documentation is for an unsupported version of PostgreSQL.

You may want to view the same page for the current version, or one of the supported versions listed above instead.

You may want to view the same page for the current version, or one of the supported versions listed above instead.

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