Aggregate functions compute a single result value from a set of input values. The built-in aggregate functions are listed in Table 9.37, “General-Purpose Aggregate Functions” and Table 9.38, “Aggregate Functions for Statistics”. The special syntax considerations for aggregate functions are explained in Section 4.2.7, “Aggregate Expressions”. Consult Section 2.7, “Aggregate Functions” for additional introductory information.
Table 9.37. General-Purpose Aggregate Functions
It should be noted that except for count
,
these functions return a null value when no rows are selected. In
particular, sum
of no rows returns null, not
zero as one might expect. The coalesce
function may be
used to substitute zero for null when necessary.
Boolean aggregates bool_and
and
bool_or
correspond to standard SQL aggregates
every
and any
or
some
.
As for any
and some
,
it seems that there is an ambiguity built into the standard syntax:
SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;
Here ANY
can be considered both as leading
to a subquery or as an aggregate if the select expression returns 1 row.
Thus the standard name cannot be given to these aggregates.
Users accustomed to working with other SQL database management
systems may be surprised by the performance of the
count
aggregate when it is applied to the
entire table. A query like:
SELECT count(*) FROM sometable;
will be executed by PostgreSQL using a sequential scan of the entire table.
Table 9.38, “Aggregate Functions for Statistics” shows
aggregate functions typically used in statistical analysis.
(These are separated out merely to avoid cluttering the listing
of more-commonly-used aggregates.) Where the description mentions
N
, it means the
number of input rows for which all the input expressions are non-null.
In all cases, null is returned if the computation is meaningless,
for example when N
is zero.
Table 9.38. Aggregate Functions for Statistics