Aggregate functions compute a single result value from a set of input values.

The following tables list the built-in aggregate functions.

Table 1. General-purpose aggregate functions
Function Argument type Return type Description
AVG(expression) INTEGER, REAL, DOUBLE PRECISION, or NUMBER NUMBER for any integer-type argument, DOUBLE PRECISION for a floating-point argument, otherwise the same as the argument data type Returns the average (arithmetic mean) of all input values.
COUNT(*) BIGINT Returns the number of input rows.
COUNT(expression) Any BIGINT Returns the number of input rows for which the value of expression is not null.
MAX(expression) Any numeric, string, or date/time type Same as argument type Returns the maximum value of expression across all input values.
MIN(expression) Any numeric, string, or date/time type Same as argument type Returns the minimum value of expression across all input values.
SUM(expression) INTEGER, REAL, DOUBLE PRECISION, or 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 Returns the sum of expression across all input values.

Note that except for the COUNT function, these functions return a null value when no rows are selected. In particular, SUM of no rows returns null, instead of returning 0 as expected. When necessary, you can use the COALESCE function to substitute zero for null.

The following table shows aggregate functions that are used in statistical analysis. (These functions are separated out to avoid cluttering the listing of more-commonly-used aggregates.) N mentioned in any description indicates the number of input rows for which all the input expressions are not null. In all cases, null is returned if the computation is invalid (for example, when N is 0).

Table 2. Aggregate functions for statistics
Function Argument type Return type Description
CORR( Y, X) DOUBLE PRECISION DOUBLE PRECISION The correlation coefficient.
COVAR POP( Y, X) DOUBLE PRECISION DOUBLE PRECISION The population covariance.
COVAR SAMP( Y, X) DOUBLE PRECISION DOUBLE PRECISION The sample covariance.
REGR AVGX( Y, X) DOUBLE PRECISION DOUBLE PRECISION The average of the independent variable (sum(X) / N).
REGR AVGY(Y, X) DOUBLE PRECISION DOUBLE PRECISION The average of the dependent variable (sum(Y) / N).
REGR COUNT( Y, X) DOUBLE PRECISION DOUBLE PRECISION The number of input rows in which both expressions are not null.
REGR INTERCEPT(Y, X) DOUBLE PRECISION DOUBLE PRECISION The y-intercept of the least-squares-fit linear equation determined by the (X, Y) pairs.
REGR R2( Y, X) DOUBLE PRECISION DOUBLE PRECISION The square of the correlation coefficient.
REGR SLOPE( Y, X) DOUBLE PRECISION DOUBLE PRECISION The 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, or NUMBER DOUBLE PRECISION for floating-point arguments, otherwise NUMBER The historical alias for STDDEV SAMP.
STDDEV POP(expression) INTEGER, REAL, DOUBLE PRECISION, or NUMBER DOUBLE PRECISION for floating-point arguments, otherwise NUMBER The population standard deviation of the input values.
STDDEV SAMP(expression) INTEGER, REAL, DOUBLE PRECISION, or NUMBER DOUBLE PRECISION for floating-point arguments, otherwise NUMBER The sample standard deviation of the input values.
VARIANCE(expression) INTEGER, REAL, DOUBLE PRECISION, or NUMBER DOUBLE PRECISION for floating-point arguments, otherwise NUMBER The historical alias for VAR SAMP.
VAR POP(expression) INTEGER, REAL, DOUBLE PRECISION, or NUMBER DOUBLE PRECISION for floating-point arguments, otherwise NUMBER The population variance of the input values (square of the population standard deviation).
VAR SAMP(expression) INTEGER, REAL, DOUBLE PRECISION, or NUMBER DOUBLE PRECISION for floating-point arguments, otherwise NUMBER The sample variance of the input values (square of the sample standard deviation).