Set Function (set_function_spec) 

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).

Syntax

<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

Examples

SQL Tutorial, Set Functions, Creating Groups: GROUP BY, HAVING, Information About Character Strings

Explanation

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.

<set_function_name>

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).

ALL/DISTINCT Function

	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

<all_function>

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.

<distinct_function>

The DISTINCT function (distinct_function) is a set function that removes duplicate 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.