Aggregate functions compute a single result value from a set of input values.
The following tables list the built-in 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).
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). |