Aggregate Functions v11

Aggregate functions compute a single result value from a set of input values. The built-in aggregate functions are listed in the following tables.

FunctionArgument TypeReturn TypeDescription
AVG (expression)INTEGER, REAL, DOUBLE PRECISION, NUMBERNUMBER for any integer type, DOUBLE PRECISION for a floating-point argument, otherwise the same as the argument data typeThe average (arithmetic mean) of all input values
COUNT(*)BIGINTNumber of input rows
COUNT (expression)AnyBIGINTNumber of input rows for which the value of expression is not null
MAX (expression)Any numeric, string, date/time, or bytea typeSame as argument typeMaximum value of expression across all input values
MIN (expression)Any numeric, string, date/time, or bytea typeSame as argument typeMinimum value of expression across all input values
SUM (expression)INTEGER, REAL, DOUBLE PRECISION, NUMBERBIGINT for SMALLINT or INTEGER arguments, NUMBER for BIGINT arguments, DOUBLE PRECISION for floating-point arguments, otherwise the same as the argument data typeSum 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.

The following table shows the 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.

FunctionArgument TypeReturn TypeDescription
CORR(Y, X)DOUBLE PRECISIONDOUBLE PRECISIONCorrelation coefficient
COVAR_POP(Y, X)DOUBLE PRECISIONDOUBLE PRECISIONPopulation covariance
COVAR_SAMP(Y, X)DOUBLE PRECISIONDOUBLE PRECISIONSample covariance
REGR_AVGX(Y, X)DOUBLE PRECISIONDOUBLE PRECISIONAverage of the independent variable (sum(X) / N)
REGR_AVGY(Y, X)DOUBLE PRECISIONDOUBLE PRECISIONAverage of the dependent variable (sum(Y) / N)
REGR_COUNT(Y, X)DOUBLE PRECISIONDOUBLE PRECISIONNumber of input rows in which both expressions are nonnull
REGR_INTERCEPT(Y, X)DOUBLE PRECISIONDOUBLE PRECISIONy-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs
REGR_R2(Y, X)DOUBLE PRECISIONDOUBLE PRECISIONSquare of the correlation coefficient
REGR_SLOPE(Y, X)DOUBLE PRECISIONDOUBLE PRECISIONSlope of the least-squares-fit linear equation determined by the (X, Y) pairs
REGR_SXX(Y, X)DOUBLE PRECISIONDOUBLE PRECISIONSum (X2) – sum (X) 2 / N (“sum of squares” of the independent variable)
REGR_SXY(Y, X)DOUBLE PRECISIONDOUBLE PRECISIONSum (X*Y) – sum (X) * sum (Y) / N (“sum of products” of independent times dependent variable)
REGR_SYY(Y, X)DOUBLE PRECISIONDOUBLE PRECISIONSum (Y2) – sum (Y) 2 / N (“sum of squares” of the dependent variable)
STDDEV(expression)INTEGER, REAL, DOUBLE PRECISION, NUMBERDOUBLE PRECISION for floating-point arguments, otherwise NUMBERHistoric alias for STDDEV_SAMP
STDDEV_POP(expression)INTEGER, REAL, DOUBLE PRECISION, NUMBERDOUBLE PRECISION for floating-point arguments, otherwise NUMBERPopulation standard deviation of the input values
STDDEV_SAMP(expression)INTEGER, REAL, DOUBLE PRECISION, NUMBERDOUBLE PRECISION for floating-point arguments, otherwise NUMBERSample standard deviation of the input values
VARIANCE(expression)INTEGER, REAL, DOUBLE PRECISION, NUMBERDOUBLE PRECISION for floating-point arguments, otherwise NUMBERHistorical alias for VAR_SAMP
VAR_POP(expression)INTEGER, REAL, DOUBLE PRECISION, NUMBERDOUBLE PRECISION for floating-point arguments, otherwise NUMBERPopulation variance of the input values (square of the population standard deviation)
VAR_SAMP(expression)INTEGER, REAL, DOUBLE PRECISION, NUMBERDOUBLE PRECISION for floating-point arguments, otherwise NUMBERSample variance of the input values (square of the sample standard deviation)