8th August 2019: PostgreSQL 11.5, 10.10, 9.6.15, 9.5.19, 9.4.24, and 12 Beta 3 Released!

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.

PostgreSQL 8.0.26 Documentation | ||||
---|---|---|---|---|

Prev | Fast Backward | Chapter 2. The SQL Language | Fast Forward | Next |

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.

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

SELECT max(temp_lo) FROM weather;

max ----- 46 (1 row)

If we wanted to know what city (or cities) that reading occurred in, we might try

SELECT city FROM weather WHERE temp_lo = max(temp_lo);WRONG

but this will not work since the aggregate `max`

cannot be used in the `WHERE` clause. (This restriction exists because the
`WHERE` clause determines the rows that
will go into the aggregation stage; so it has to be evaluated
before aggregate functions are computed.) However, as is often
the case the query can be restated to accomplish the intended
result, here by using a *subquery*:

SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

city --------------- San Francisco (1 row)

This is OK because the subquery is an independent computation that computes its own aggregate separately from what is happening in the outer query.

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;

city | max ---------------+----- Hayward | 37 San Francisco | 46 (2 rows)

which gives us one output row per city. Each aggregate result
is computed over the table rows matching that city. We can filter
these grouped rows using `HAVING`:

SELECT city, max(temp_lo) FROM weather GROUP BY city HAVING max(temp_lo) < 40;

city | max ---------+----- Hayward | 37 (1 row)

which gives us the same results for only the cities that have
all `temp_lo` values below 40. Finally, if
we only care about cities whose names begin with "`S`", we might do

SELECT city, max(temp_lo) FROM weather WHERE city LIKE 'S%'(1) GROUP BY city HAVING max(temp_lo) < 40;

- (1)
- The
`LIKE`operator does pattern matching and is explained in Section 9.7.

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 must 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, the
`HAVING` clause always contains 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.)

In the previous example, we can apply the city name
restriction in `WHERE`, since it needs no
aggregate. This is more efficient than adding the restriction to
`HAVING`, because we avoid doing the
grouping and aggregate calculations for all rows that fail the
`WHERE` check.