Rules for Column Functions

Functions › Column Functions › Rules for Column Functions

Rules for Column Functions

The result of a column function is derived by the application of the column function to the specified argument.

The argument of COUNT(*) is a collection of rows.
The argument of the other column functions is a collection of values. For SUM and AVG, the values must be numbers.
The source of the argument of any column function is a group or an intermediate result table.

The following rules apply to all column functions other than COUNT(*).

A column-name in a column function must not reference a column derived from a column function (a column of a view can be derived from a column function).
Column functions cannot be nested. Thus, an expression in a column function must not include a column function.
An expression in a column function must include a column-name. If the column-name is a correlated reference (which is allowed in a subquery of a HAVING clause), the expression must not include any operators.
Before a column function is applied, null values are eliminated from its argument.
- If DISTINCT is specified, redundant duplicate values are also eliminated.
- If ALL is specified, duplicates are not eliminated.
- If neither ALL nor DISTINCT is specified, duplicates are not eliminated.

The following table lists each column function, the result of each, the data type of the result, and any exceptions.

Column Functions:

Column Function	Description
AVG	Results: The average of the values in its arguments. The values must be numbers. If the values are binary integers, the fractional part of the average is lost. Data Type: The same as the data type of its argument. Exception: The result is a large integer if the data type of the argument is a small integer. If the data type of the argument is decimal with precision p and scale s, the precision of the result is 31 and the scale is 31 - p + s. The sum of the values of the argument must be within the range of the result data type.
COUNT(*)	Results: The number of rows in its argument. Data Type: The result is always a large integer which cannot have a null value.
COUNT(DISTINCT column-name)	Results: The number of values in its argument, that is to say, the number of distinct values of the column in the group or intermediate result table. Data Type: The result is always a large integer.
MAX	Results: The maximum value in its argument. Data Type: The same as the data type of its argument.
MIN	Results: The minimum value in its argument. Data Type: The same as the data type of its argument.
SUM	Results: The sum of the values in its arguments. Data Type: The same as the data type of its argument. The values must be numbers. Exception: The result is a large integer if the data type of the argument is a small integer. If the data type of the argument is decimal, the precision of the result is 31 and the scale is the same as the scale of the argument. The sum of the values must be within the range of the result data type.

Column Function

Description

AVG

Results:
The average of the values in its arguments.

The values must be numbers.

If the values are binary integers, the fractional part of the average is lost.

Data Type:
The same as the data type of its argument.

Exception:
The result is a large integer if the data type of the argument is a small integer.

If the data type of the argument is decimal with precision p and scale s, the precision of the result is 31 and the scale is 31 - p + s.

The sum of the values of the argument must be within the range of the result data type.

COUNT(*)

Results:
The number of rows in its argument.

Data Type:
The result is always a large integer which cannot have a null value.

COUNT(DISTINCT column-name)

Results:
The number of values in its argument, that is to say, the number of distinct values of the column in the group or intermediate result table.

Data Type:
The result is always a large integer.

MAX

Results: The maximum value in its argument.

Data Type:
The same as the data type of its argument.

MIN

Results: The minimum value in its argument.

Data Type:
The same as the data type of its argument.

SUM

Results:
The sum of the values in its arguments.

Data Type:
The same as the data type of its argument.

The values must be numbers.

Exception:
The result is a large integer if the data type of the argument is a small integer.

If the data type of the argument is decimal, the precision of the result is 31 and the scale is the same as the scale of the argument.

The sum of the values must be within the range of the result data type.