2.4.11 Aggregate Functions

Table of Contents Previous Next


2 The SQL Language : 2.4 Functions and Operators : 2.4.11 Aggregate Functions

Aggregate 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)
Sum (X2) – sum (X)2 / N (“sum of squares” of the independent variable)
Sum (X*Y) – sum (X) * sum (Y) / N (“sum of products” of independent times dependent variable)
Sum (Y2) – sum (Y)2 / N (“sum of squares” of the dependent variable)
STDDEV(expression)
DOUBLE PRECISION for floating-point arguments, otherwise NUMBER
STDDEV_POP(expression)
DOUBLE PRECISION for floating-point arguments, otherwise NUMBER
STDDEV_SAMP(expression)
DOUBLE PRECISION for floating-point arguments, otherwise NUMBER
VARIANCE(expression)
DOUBLE PRECISION for floating-point arguments, otherwise NUMBER
VAR_POP(expression)
DOUBLE PRECISION for floating-point arguments, otherwise NUMBER
VAR_SAMP(expression)
DOUBLE PRECISION for floating-point arguments, otherwise NUMBER

2 The SQL Language : 2.4 Functions and Operators : 2.4.11 Aggregate Functions

Table of Contents Previous Next