PostgreSQL, Second Edition

Aggregates

PostgreSQL offers a number of aggregate functions. An aggregate is a collection of things—you can think of an aggregate as the set of rows returned by a query. An aggregate function is a function that operates on an aggregate (nonaggregate functions operate on a single row within an aggregate). Most of the aggregate functions operate on a single value extracted from each row—this is called an aggregate expression.

COUNT()

COUNT() is probably the simplest aggregate function. COUNT() returns the number of objects in an aggregate. The COUNT() function comes in four forms:

• COUNT(*)

• COUNT( expression )

• COUNT( ALL expression )

• COUNT( DISTINCT expression )

In the first form, COUNT(*) returns the number of rows in an aggregate:

 movies=# SELECT ...