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

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

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

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

### Standard Compliance

This section summarizes standard compliance for set functions.

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