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

Structure

Syntax 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

End of the code.

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. However, this 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.

If isolation level 1 is set for the database user, no locks are considered for SELECT COUNT(*) statements without any further qualification, but locks are considered for SELECT COUNT(*) statements with further qualifications.

<set_function_name>

The set functions are classified by the following set function names:

Set Function (set_function_spec)
`AVG`	The result of `AVG` is the arithmetical mean of the values of the argument. `AVG` can only be applied to numerical 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 are different from the `NULL` value (number of values in the argument of the `ALL` function). The result has the data type FIXED(20).
`MAX`	The result of `MAX` is the largest value of the argument.
`MIN`	The result of `MIN` is the largest 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 numerical 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 numerical 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 numerical values. The result has the data type `FLOAT(38)`.

ALL/DISTINCT Function

Set Function (set_function_spec)
	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 all NULL values.

The argument of a ALL function is a set of values that is calculated as follows:

A result table or group (the result table can be grouped with a GROUP condition) is formed.
An expression is applied to each row in this result table or group. The expression must not contain a set function.
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:

A result table or group (the result table can be grouped with a GROUP condition) is formed.
An expression is applied to each row in this result table or group. The expression must not contain a set function.
All 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.

More Information

Functions: Overview