About Set Functions

---

Set functions are pre-defined functions used in select specifications. They operate on the set of values in one column of the result of the SELECT statement, or on the subset in a group if the statement includes a GROUP BY clause.

The result of a set function is a single value for each operand set.

Syntax for Set Functions

The general syntax for a set function is:

AVG

Returns the average of the values in the set.

Note: AVG can only be applied to numerical values.

COUNT

Returns the number of values in the set.

MAX

Returns the largest value in the set.

MIN

Returns the smallest value in the set.

SUM

Returns the sum of the values in the set.

Note: SUM can only be applied to numerical values.

Examples

 SELECT MIN(PRICE) AS INEXPENSIVE, MAX(PRICE) AS EXPENSIVE
 FROM   ROOM_PRICES WHERE HOTELCODE = 'LAP'
 
 SELECT HOTELCODE, AVG(PRICE) AS AVERAGE_PRICE
 FROM   ROOM_PRICES
 GROUP BY HOTELCODE
 
 SELECT COUNT(*) FROM SOME_TABLE

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.

Restrictions

Column references in the argument of a set function may not address view columns which are themselves derived from set functions.

The argument of a set function must contain at least one column reference and cannot contain any set function references. If the column is an outer reference, then the expression should not include any operators.

If a set function contains a column that is an outer reference, then the set function must be contained in a subselect of a HAVING clause.

Results of Set Functions

When the argument of a set function is a numerical value, the precision and scale of the set function result is evaluated in accordance with the rules given below. If the argument is an expression, the expression is first evaluated as described in Expressions before the set function is applied.

Evaluating Set Functions

	FLOAT(p')	INTEGER(p')	DECIMAL(p',s')
SUM	FLOAT(p)1	INTEGER(p)2	DECIMAL(p,s)3
AVG	FLOAT(p)a	INTEGER(p)4	DECIMAL(p,s)5
MAX, MIN	FLOAT(p)d	INTEGER(p)d	DECIMAL(p,s)6
COUNT	INTEGER(10)	INTEGER(10)	INTEGER(10)

1p=max(15, p') 2p=min(45, 10+p') 3p=min(45, 10+p') s=s' 4p=p' 5p=min(45, 10+p') s=p-(p'-s') 6p=p' s=s'

The following examples show how some set functions are evaluated.

AVG(SMALLINT) gives SMALLINT

AVG(INTEGER) gives INTEGER

AVG(DECIMAL(38,10)) gives DECIMAL(45,17)

AVG(DECIMAL(4,2)) gives DECIMAL(14,12)

SUM(SMALLINT) gives INTEGER(15)

SUM(INTEGER) gives INTEGER(20)

SUM(DECIMAL(38,10)) gives DECIMAL(45,10)

SUM(DECIMAL(4,2)) gives DECIMAL(14,2)

Note: Often, the average of a series of integers is required as a decimal rather than an integer. This may be achieved by casting the value to a decimal using the CAST function.
For example, if the values in the integer column COL are 1, 3 and 6, then AVG(COL) returns 3 but AVG(CAST(COL as decimal(5,4))) returns 3.3333.

Standard Compliance

This section summarizes standard compliance for set functions.

Standard	Compliance	Comments
SQL-99	Core	Fully compliant.
SQL-99	Features outside core	Feature F441, "Extended set function support". Feature F561, "Full value expressions" use of DISTINCT expression in set function, where expression is not a column.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se