## Functions

This chapter discusses set functions and scalar 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 and interval 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 and interval 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:

## 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`DISTINCT`

keyword makes no difference to the result. (The same value will be returned with or without`DISTINCT`

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

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

The following examples show how some set functions are evaluated.

`AVG(DECIMAL(38,10))`

gives`DECIMAL(45,17)`

`AVG(DECIMAL(4,2))`

gives`DECIMAL(14,12)`

`AVG(INTERVAL YEAR(2) TO MONTH)`

gives`INTERVAL YEAR(2) TO MONTH`

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

`SUM(INTERVAL YEAR(2) TO MONTH)`

gives`INTERVAL YEAR(2) TO MONTH`

## 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(14,4))) returns 3.33333333333333.## Alternatively, multiply the AVG argument by 1.0, i.e. AVG(COL * 1.0).

## Standard Compliance

This section summarizes standard compliance for set functions.

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