www.mimer.com/developer

Available Set Functions

---

The available set functions are:

Set function	Result
AVG	Average of the values in the set.
COUNT	Number of values in the set.
MAX	Largest value in the set.
MIN	Smallest value in the set.
SUM	Sum of the values in the set.

Note: The functions SUM and AVG can only be applied to numerical values.

General Syntax

The general syntax for the set-function is:

Operational Mode

The operational mode of a set function is determined by the use of the keywords ALL and DISTINCT.

When ALL is specified or no keyword is used:

• Any duplicate values in the operand set are retained.

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 MAX and MIN, the keyword DISTINCT will be ignored if it is used.

NULL Values

For all set functions except COUNT(*), any NULL values in the operand set are eliminated before the set function is applied, regardless of whether DISTINCT is specified or not.

The special form COUNT (*) returns the number of rows in the result table, including any NULL values. The keywords ALL and DISTINCT may not be used with this form of COUNT.

If the operand set is empty, the COUNT function returns the value zero. All other functions return NULL for an empty operand set.

The COUNT function returns an integer with precision 10. The MAX and MIN functions return a value with the same type and precision as the operand. The precision of the result returned by SUM and AVG is considered below.

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se