Aggregate functions calculate a single value for a collection of
rows in a result table. Aggregate functions are sometimes called
statistical functions.
The following notes generally apply to aggregate functions. An
aggregate function is a single value derived from one or more
sets of values.
o A value expression is used to evaluate a value for each row.
The aggregate function operates on these values.
o Null values are not included when SQL evaluates functions. If
you specify DISTINCT, redundant values are also not included.
If you have set the dialect to SQL99, this null elimination
causes a warning to be returned for the SQLCODE or SQLSTATE.
See the Oracle Rdb SQL Reference Manual for more information
on SQLSTATE and SQLCODE.
o If a function has as its argument a value expression that
contains a column name that is an outer reference (see Outer_
References), the value expression cannot include an arithmetic
operator. (The only cases where an outer reference makes sense
as the argument to a function is in the subquery of a HAVING
clause or in a subquery in a select list.)
o You cannot nest functions. This means that a value expression
used as an argument to a function cannot include a function.
o The keyword ALL in SUM, AVG, MAX, and MIN has no effect. For
instance, specifying MAX (ALL EMPLOYEE_ID) is the same as
saying MAX (EMPLOYEE_ID).
o VARIANCE and STDDEV must be passed a single numeric value
expression and the result is returned as a DOUBLE PRECISION
value. Use the CAST function to alter the result data type.
o VARIANCE, and hence STDDEV, assume that one degree of freedom
is used in the calculation of the mean (average) and therefore
the divisor is specified as (n-1). For a large number of
values in the statistical sample this will not be significant.
However, for small samples it may not be desirable, so this
default can be changed for the current session by using the
SET FLAGS option VARIANCE_DOF(0). Only the values 0 and 1 are
allowed.
o The keywords ALL and DISTINCT are not permitted when using
the VAR_POP, VAR_SAMP, STDDEV_POP and STDDEV_SAMP statistical
functions.
The FILTER clause is provided for all statistical functions. This
clause can be used to limit the values included in the COUNT,
MAX, MIN, SUM, AVG, STDDEV, and VARIANCE functions.
FILTER can be used to eliminate data from the statistical
function so that the generated report can process the data in
a single pass. The following example illustrates how the FILTER
clause is applied.
SQL> select
cont> max (salary_amount) filter (where salary_end is null),
cont> max (salary_amount) filter (where salary_end is not null),
cont> min (distinct salary_amount) filter (where salary_end = salary_start),
cont> min (distinct salary_amount) filter (where salary_end > salary_start)
cont> from
cont> salary_history
cont> where
cont> employee_id = '00164'
cont> group by
cont> employee_id;
51712.00 50000.00 NULL 26291.00
1 row selected
SQL>
1 – COUNT Function
There are three forms of the COUNT function:
o COUNT (*) calculates the number of rows in a result table.
It is the only function that does not allow a specific column
name in its argument. The data type of the resulting value
expression is an integer.
o COUNT (value-expr) calculates the number of non-NULL values of
the value-expr in a result table. The value-expr is evaluated
for each row and, if non-NULL, the count is incremented or the
value is counted. The data type of the resulting value is an
integer.
o COUNT (DISTINCT value-expr) calculates the number of distinct
values of the specified value-expr in the result table. The
COUNT DISTINCT function eliminates duplicate values from the
number it calculates. The value-expr is evaluated for each row
and, if non-NULL and if different from previously seen values,
the value is counted. It does not count null values in the
specified value-expr. The data type of the resulting value is
an integer.
If there are no values in the result table to which the COUNT
function is applied, the COUNT function returns a zero.
2 – SUM Function
The SUM function calculates the total of the values specified by
the value expression in its argument. If there are no rows in the
result table to which the SUM function is applied, it returns a
null value.
The SUM function must refer to a value with a numeric or INTERVAL
data type. It returns a value of the same general data type
(fixed- or floating-point) big enough to store the result.
If your dialect is set to an ANSI/ISO SQL standard, a warning
message is returned if any of the values is NULL.
3 – AVG Function
The AVG function calculates the average of the values specified
by the value expression in its argument. If there are no rows in
the result table to which the AVG function is applied, it returns
a null value.
The AVG function must refer to a value with a numeric or INTERVAL
data type. The value it returns is a floating-point data type for
numeric expressions, or an INTERVAL data type.
If a value is NULL, the row is treated as missing and, if your
dialect is set to an ANSI/ISO SQL standard, a warning message is
returned.
4 – MAX Function
The MAX function calculates the largest of the values specified
by the value expression in its argument. If there are no values
in the result table to which the MAX function is applied, it
returns a null value.
The MAX function returns a value of the same data type as the
value in its argument for all data types except LIST OF BYTE
VARYING.
5 – MIN Function
The MIN function returns the smallest of the values specified by
the value expression in its argument. If there are no values in
the result table to which the MIN function is applied, it returns
a null value.
The MIN function returns a value of the same data type as the
column in its argument for all data types except LIST OF BYTE
VARYING.
6 – STDDEV Functions
The STDDEV (standard deviation) function calculates the square
root of the variance and is expressed in the same units as the
source expression.
Oracle Rdb provides the following statistical functions to
standard deviation:
o STDDEV
This function calculates the standard deviation.
o STDDEV_POP
This function calculates the standard deviation (the square
root of the variance) for the population. It is equivalent to
STDDEV with degrees of freedom fixed at 0, that is, SET FLAGS
'VARIANCE_DOF(0)' which is the default setting.
o STDDEV_SAMP
This function calculates the standard deviation (the square
root of the variance) for the subset of sampling of the
population. It is equivalent to STDDEV with degrees of freedom
fixed at 1, that is, SET FLAGS 'VARIANCE_DOF(1)' which is the
default setting. By convention one degree of freedom is used
when the sampling of the population is performed.
7 – VARIANCE Functions
The VARIANCE function calculates the variablity from the mean (or
average) value.
Oracle Rdb provides the following variance statistical functions:
o VARIANCE
Calculates the variance of the value set.
o VAR_POP
This function calculates the variance for the population. It
is equivalent to VARIANCE with degrees of freedom fixed at
0, that is, SET FLAGS 'VARIANCE_DOF(0)' which is the default
setting.
o VAR_SAMP
This function calculates the variance for a subset or sampling
of the population. It is equivalent to VARIANCE with degrees
of freedom fixed at 1, that is, SET FLAGS 'VARIANCE_DOF(1)'.
By convention one degree of freedom is used when the sampling
of the population is performed.