Available Set Functions
The available set functions are:
AVG Average of the values in the set. COUNT Number of values in the set. MAX Largest value in the set. MIN Smallest value in the set. SUM Sum of the values in the set.
Note: The functions SUM and AVG can only be applied to numerical values.
The general syntax for the set-function is:
The operational mode of a set function is determined by the use of the keywords ALL and DISTINCT.
When ALL is specified or no keyword is used:
When DISTINCT is specified:
- Redundant duplicate values are eliminated from the operand set before the function is applied.
- The result of the set function must not be combined with other terms using binary arithmetic operators.
- For the set functions MAX and MIN, the keyword DISTINCT will be ignored if it is used.
For all set functions except COUNT(*), any NULL values in the operand set are eliminated before the set function is applied, regardless of whether DISTINCT is specified or not.
The special form COUNT (*) returns the number of rows in the result table, including any NULL values. The keywords ALL and DISTINCT may not be used with this form of COUNT.
If the operand set is empty, the COUNT function returns the value zero. All other functions return NULL for an empty operand set.
The COUNT function returns an integer with precision 10. The MAX and MIN functions return a value with the same type and precision as the operand. The precision of the result returned by SUM and AVG is considered below.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40