There is a series of functions that can be applied to a set of values (rows) as an argument and supply a result. These functions are referred to as set functions (set_function_spec).
<set_function_spec> ::= COUNT(*)
| <all_function>
| <distinct_function>
<all_function> ::= <set_function_name> ( ALL <expression> )
<distinct function>::= <set function name> ( DISTINCT <expression> )
<set_function_name> ::= AVG | COUNT | MAX | MIN | STDDEV | SUM | VARIANCE
Set functions operate across groups of values but only return one value. The result comprises one row. If a set function is used in a statement, a similar function must also be applied to each of the other columns in the request. This, however, does not apply to columns that were grouped using GROUP BY. In this case, the value of the set function can be defined for each group.
The argument of a DISTINCT function or an ALL function is a result table or a group (the result table can be grouped using a GROUP condition).
With the exception of the COUNT(*) function, no NULL values are included in the calculation.
No locks are set for certain set functions, irrespective of the isolation level specified when the user connected to the database.
SELECT
SUM(price) sum_price, MIN(price) min_price, FIXED(AVG(price),7,2) avg_price,
MAX(price) max_price, COUNT(*) number
FROM room
WHERE type = 'single'
Calculations for single room: sum of the prices, lowest price, average price, highest price, number of single rooms
SUM_PRICE |
MIN_PRICE |
AVG_PRICE |
MAX_PRICE |
NUMBER |
1450 |
45 |
96.67 |
160 |
15 |
SELECT
COUNT(*) number, FIXED (AVG(price),7,2) avg_price
FROM room
WHERE type = 'suite'
How many rooms that are suites does the list contain? What is the average price?
NUMBER |
AVG_PRICE |
8 |
475 |
SELECT
COUNT(*) number
FROM customer
How many customers are there?
NUMBER |
15 |
The set functions are classified by the following set function names:
AVG |
The result of AVG is the arithmetical mean of the values of the argument. AVG can only be applied to numeric values. The result has the data type FLOAT(38). |
COUNT |
- COUNT(*) supplies the total number of values (rows in a result table or group). - COUNT(DISTINCT <expression>) supplies the total number of different values (number of values in the argument of the DISTINCT function). - COUNT(ALL <expression>) supplies the number of values that differ from the NULL value (number of values in the argument of the ALL function). The result has the data type FIXED(10). |
MAX |
The result of MAX is the largest value of the argument. |
MIN |
The result of MIN is the smallest value of the argument. |
STDDEV |
The result of STDDEV is the standard deviation of the values of the argument. STDDEV can only be applied to numeric values. The result has the data type FLOAT(38). |
SUM |
The result of SUM is the sum of the values of the argument. SUM can only be applied to numeric values. The result has the data type FLOAT(38). |
VARIANCE |
The result of VARIANCE is the variance of the values of the argument. VARIANCE can only be applied to numeric values. The result has the data type FLOAT(38). |
|
Result of the ALL/DISTINCT Function |
The set of values is empty and the ALL/DISTINCT function is applied to the entire result table |
The set functions AVG, MAX, MIN, STDDEV, SUM, VARIANCE supply the NULL value as their result. The set function COUNT supplies the value 0. |
There is no group to which the ALL/DISTINCT function can be applied. |
The result is an empty table. |
The set of values contains at least one special NULL value. |
Special NULL value |
The ALL function (all_function) is a set function that removes the NULL values.
The argument of an ALL function is a set of values that is calculated as follows:
1. A result table or group (the result table can be grouped with a GROUP condition) is formed.
2.
The expression (expression) is
applied to each row in this result table or group.
The expression must not contain a set function.
3. All NULL values are removed. Special NULL values are not removed; two special NULL values are regarded as identical.
The ALL function is executed taking into account the relevant set function name (set_function_name) for the set of values.
The result of an ALL function is independent of whether the keyword ALL is specified or not.
The DISTINCT function (distinct_function) is a set function that removes duplicated values and all NULL values.
The argument of a DISTINCT function is a set of values that is calculated as follows:
...
1. A result table or group (the result table can be grouped with a GROUP condition) is formed.
2.
The expression (expression) is
applied to each row in this result table or group.
The expression must not contain a set function.
3. All of the NULL values and duplicated values are removed (DISTINCT). Special NULL values are not removed; two special NULL values are regarded as identical.
The DISTINCT function is executed taking into account the relevant set function name (set_function_name) for this set of values.
SELECT COUNT(DISTINCT zip) number_zip FROM customer
In how many cities with different zip codes do customers live?
NUMBER_ZIP |
12 |