This chapter discusses set functions and scalar 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
SELECTstatement, or on the subset in a group if the statement includes a
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 and interval 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 and interval 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
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
DISTINCTkeyword makes no difference to the result. (The same value will be returned with or without
For all set functions except
NULLvalues in the operand set are eliminated before the set function is applied, regardless of whether
DISTINCTis specified or not.
The special form
COUNT(*)returns the number of rows in the result table, including any
NULLvalues. The keywords
DISTINCTmay not be used with this form of
If the operand set is empty, the
COUNTfunction returns the value zero. All other functions return
NULLfor an empty operand set.
COUNTfunction returns an
MINfunctions return a value with the same type and precision as the operand. The precision of the result returned by
AVGis 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
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
3p=min(45, 10+p') s=s'
The following examples show how some set functions are evaluated.
AVG(INTERVAL YEAR(2) TO MONTH)gives
INTERVAL YEAR(2) TO MONTH
SUM(INTERVAL YEAR(2) TO MONTH)gives
INTERVAL YEAR(2) TO MONTH
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(14,4))) returns 3.33333333333333.
Alternatively, multiply the AVG argument by 1.0, i.e. AVG(COL * 1.0).
This section summarizes standard compliance for set functions.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40