Aggregate functions compute a single
result value from a set of input values. The built-in aggregate
functions are listed in Table
9-37 and Table
9-38. The special syntax considerations for aggregate
functions are explained in Section 4.2.7.
Consult Section 2.7 for
additional introductory information.

Table 9-37. General-Purpose Aggregate
Functions

Function

Argument Type

Return Type

Description

avg(expression)

smallint, int, bigint, real, double precision,
numeric, or interval

numeric for any integer type
argument, double precision for a
floating-point argument, otherwise the same as the
argument data type

the average (arithmetic mean) of all input
values

bit_and(expression)

smallint, int, bigint, or
bit

same as argument data type

the bitwise AND of all non-null input values, or null
if none

bit_or(expression)

smallint, int, bigint, or
bit

same as argument data type

the bitwise OR of all non-null input values, or null
if none

bool_and(expression)

bool

bool

true if all input values are true, otherwise
false

bool_or(expression)

bool

bool

true if at least one input value is true, otherwise
false

count(*)

bigint

number of input rows

count(expression)

any

bigint

number of input rows for which the value of
expression is not
null

every(expression)

bool

bool

equivalent to bool_and

max(expression)

any array, numeric, string, or date/time type

same as argument type

maximum value of expression across all input
values

min(expression)

any array, numeric, string, or date/time type

same as argument type

minimum value of expression across all input
values

sum(expression)

smallint, int, bigint, real, double precision,
numeric, or interval

bigint for smallint or int
arguments, numeric for bigint arguments, double
precision for floating-point arguments, otherwise
the same as the argument data type

sum of expression
across all input values

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.

Note: 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.

Note: 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 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

Function

Argument Type

Return Type

Description

corr(Y,
X)

double precision

double precision

correlation coefficient

covar_pop(Y,
X)

double precision

double precision

population covariance

covar_samp(Y,
X)

double precision

double precision

sample covariance

regr_avgx(Y, X)

double precision

double precision

average of the independent variable (sum(X)/N)

regr_avgy(Y, X)

double precision

double precision

average of the dependent variable (sum(Y)/N)

regr_count(Y, X)

double precision

bigint

number of input rows in which both expressions are
nonnull

regr_intercept(Y, X)

double precision

double precision

y-intercept of the least-squares-fit linear equation
determined by the (X,
Y) pairs

regr_r2(Y, X)

double precision

double precision

square of the correlation coefficient

regr_slope(Y,
X)

double precision

double precision

slope of the least-squares-fit linear equation
determined by the (X,
Y) pairs

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.