24th June 2021:
PostgreSQL 14 Beta 2 Released!

Unsupported versions:
7.1

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 other supported versions listed above instead.

You may want to view the same page for the current version, or one of the other 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 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