2.4.11 Aggregate Functions
2.4.11 Aggregate FunctionsAggregate functions compute a single result value from a set of input values. The built-in aggregate functions are listed in the following tables.
AVG(expression) NUMBER for any integer type, DOUBLE PRECISION for a floating-point argument, otherwise the same as the argument data type COUNT(expression) MAX(expression) MIN(expression) SUM(expression) BIGINT for SMALLINT or INTEGER arguments, NUMBER for BIGINT arguments, DOUBLE PRECISION for floating-point arguments, otherwise the same as the argument data type 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.
CORR(Y, X) COVAR_POP(Y, X) COVAR_SAMP(Y, X) REGR_AVGX(Y, X) REGR_AVGY(Y, X) REGR_COUNT(Y, X) REGR_INTERCEPT(Y, X) REGR_R2(Y, X) REGR_SLOPE(Y, X) REGR_SXX(Y, X) Sum (X2) – sum (X)2 / N (“sum of squares” of the independent variable) REGR_SXY(Y, X) Sum (X*Y) – sum (X) * sum (Y) / N (“sum of products” of independent times dependent variable) REGR_SYY(Y, X) Sum (Y2) – sum (Y)2 / N (“sum of squares” of the dependent variable) STDDEV(expression) Historic alias for STDDEV_SAMP STDDEV_POP(expression) STDDEV_SAMP(expression) VARIANCE(expression) Historical alias for VAR_SAMP VAR_POP(expression) VAR_SAMP(expression)