Using Aggregate Functions

Like most other query languages, PostgreSQL supports aggregate functions. The current implementation of Postgres aggregate functions have some limitations. Specifically, while there are aggregates to compute such functions as the count, sum, avg (average), max (maximum) and min (minimum) over a set of instances, aggregates can only appear in the target list of a query and not directly in the qualification (the where clause). As an example,

SELECT max(temp_lo) FROM weather;

is allowed, while

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

is not. However, as is often the case the query can be restated to accomplish the intended result; here by using a subselect:

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

Aggregates may also have group by clauses:

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