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