About Set Functions
Set functions are pre-defined functions used in select specifications. They operate on the set of values in one column of the result of the SELECT statement, or on the subset in a group if the statement includes a GROUP BY clause.
The result of a set function is a single value for each operand set.
Syntax for Set Functions
The general syntax for a set function is:
Returns the average of the values in the set.
Note: AVG can only be applied to numerical values.
Returns the number of values in the set.
Returns the largest value in the set.
Returns the smallest value in the set.
Returns the sum of the values in the set.
Note: SUM can only be applied to numerical values.
ExamplesSELECT MIN(PRICE) AS INEXPENSIVE, MAX(PRICE) AS EXPENSIVE FROM ROOM_PRICES WHERE HOTELCODE = 'LAP' SELECT HOTELCODE, AVG(PRICE) AS AVERAGE_PRICE FROM ROOM_PRICES GROUP BY HOTELCODE SELECT COUNT(*) FROM SOME_TABLE
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.
Column references in the argument of a set function may not address view columns which are themselves derived from set functions.
The argument of a set function must contain at least one column reference and cannot contain any set function references. If the column is an outer reference, then the expression should not include any operators.
If a set function contains a column that is an outer reference, then the set function must be contained in a subselect of a HAVING clause.
Results of Set Functions
When the argument of a set function is a numerical value, the precision and scale of the set function result is evaluated in accordance with the rules given below. If the argument is an expression, the expression is first evaluated as described in Expressions before the set function is applied.
Evaluating Set Functions
FLOAT(p') INTEGER(p') DECIMAL(p',s') SUM FLOAT(p)1 INTEGER(p)2 DECIMAL(p,s)3 AVG FLOAT(p)a INTEGER(p)4 DECIMAL(p,s)5 MAX, MIN FLOAT(p)d INTEGER(p)d DECIMAL(p,s)6 COUNT INTEGER(10) INTEGER(10) INTEGER(10)
3p=min(45, 10+p') s=s'
5p=min(45, 10+p') s=p-(p'-s')
The following examples show how some set functions are evaluated.
AVG(SMALLINT) gives SMALLINT
AVG(INTEGER) gives INTEGER
AVG(DECIMAL(38,10)) gives DECIMAL(45,17)
AVG(DECIMAL(4,2)) gives DECIMAL(14,12)
SUM(SMALLINT) gives INTEGER(15)
SUM(INTEGER) gives INTEGER(20)
SUM(DECIMAL(38,10)) gives DECIMAL(45,10)
SUM(DECIMAL(4,2)) gives DECIMAL(14,2)
Note: Often, the average of a series of integers is required as a decimal rather than an integer. This may be achieved by casting the value to a decimal using the CAST function.
For example, if the values in the integer column COL are 1, 3 and 6, then AVG(COL) returns 3 but AVG(CAST(COL as decimal(5,4))) returns 3.3333.
This section summarizes standard compliance for set functions.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40