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.
Function | Argument Type | Return Type | Description |
---|---|---|---|
AVG (expression) | INTEGER, REAL, DOUBLE PRECISION, NUMBER | NUMBER for any integer type, DOUBLE PRECISION for a floating-point argument, otherwise the same as the argument data type | The average (arithmetic mean) of all input values |
COUNT(*) | BIGINT | Number of input rows | |
COUNT (expression) | Any | BIGINT | Number of input rows for which the value of expression is not null |
MAX (expression) | Any numeric, string, date/time, or bytea type | Same as argument type | Maximum value of expression across all input values |
MIN (expression) | Any numeric, string, date/time, or bytea type | Same as argument type | Minimum value of expression across all input values |
SUM (expression) | INTEGER, REAL, DOUBLE PRECISION, NUMBER | BIGINT for SMALLINT or INTEGER arguments, NUMBER 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.
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.
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 | DOUBLE PRECISION | 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 |
REGR_SXX(Y, X) | DOUBLE PRECISION | DOUBLE PRECISION | Sum (X2) – sum (X) 2 / N (“sum of squares” of the independent variable) |
REGR_SXY(Y, X) | DOUBLE PRECISION | DOUBLE PRECISION | Sum (X*Y) – sum (X) * sum (Y) / N (“sum of products” of independent times dependent variable) |
REGR_SYY(Y, X) | DOUBLE PRECISION | DOUBLE PRECISION | Sum (Y2) – sum (Y) 2 / N (“sum of squares” of the dependent variable) |
STDDEV(expression) | INTEGER, REAL, DOUBLE PRECISION, NUMBER | DOUBLE PRECISION for floating-point arguments, otherwise NUMBER | Historic alias for STDDEV_SAMP |
STDDEV_POP(expression) | INTEGER, REAL, DOUBLE PRECISION, NUMBER | DOUBLE PRECISION for floating-point arguments, otherwise NUMBER | Population standard deviation of the input values |
STDDEV_SAMP(expression) | INTEGER, REAL, DOUBLE PRECISION, NUMBER | DOUBLE PRECISION for floating-point arguments, otherwise NUMBER | Sample standard deviation of the input values |
VARIANCE(expression) | INTEGER, REAL, DOUBLE PRECISION, NUMBER | DOUBLE PRECISION for floating-point arguments, otherwise NUMBER | Historical alias for VAR_SAMP |
VAR_POP(expression) | INTEGER, REAL, DOUBLE PRECISION, NUMBER | DOUBLE PRECISION for floating-point arguments, otherwise NUMBER | Population variance of the input values (square of the population standard deviation) |
VAR_SAMP(expression) | INTEGER, REAL, DOUBLE PRECISION, NUMBER | DOUBLE PRECISION for floating-point arguments, otherwise NUMBER | Sample variance of the input values (square of the sample standard deviation) |