1 Aggregate_Functions 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> 2 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. 2 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. 2 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. 2 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. 2 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. 2 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.