 ## Using Set Functions

The functions listed below can be used in the column list of the `SELECT` statement to retrieve the result of the function on a specified column.

##### Explanation
`AVG`
average of values (numerical columns only)
`COUNT`
number of rows
`MAX`
largest value
`MIN`
smallest value
`SUM`
sum of values (numerical columns only)

Set functions in `SELECT` statements are applied to data in the result table, not in the source table.

Set functions return a single value for the whole table unless a `GROUP BY` clause is specified, see Grouped Set Functions - the GROUP BY Clause.

For all set functions, `NULL` values are eliminated from the column before the function is applied. The special form `COUNT(*)` counts the number of rows including `NULL` values.

The keywords `ALL` and `DISTINCT` may be used to qualify set functions. `ALL` gives a result based on all values including duplicates. `DISTINCT` eliminates duplicates before applying the function. If neither keyword is specified, duplicates are not removed.

### Example of Set Functions

The set functions are illustrated with results from the following table:

##### Note: A hyphen - indicates NULL.

 ``` SAMPLE ``` ``` 1.0 ``` ``` 2.0 ``` ``` 2.0 ``` ``` 2.0 ``` ``` 3.0 ``` ``` 3.0 ``` ``` 4.0 ``` ``` 5.0 ``` ``` - ``` ``` - ```

``` COUNT(SAMPLE)	8
COUNT(*)	10
COUNT(DISTINCT SAMPLE)	5
SUM(SAMPLE)	22.0
SUM(ALL SAMPLE)	22.0
SUM(DISTINCT SAMPLE)	15.0
AVG(SAMPLE)	2.75000000000
AVG(ALL SAMPLE)	2.75000000000
AVG(DISTINCT SAMPLE)	3.00000000000
MAX(SAMPLE)	5.0
MIN(SAMPLE)	1.0

```
##### Thus, for the table above:
``` SUM(SAMPLE)/COUNT(SAMPLE)    2.75000000000   (22/8)
SUM(SAMPLE)/COUNT(*)         2.20000000000   (22/10)
```

### More Set Functions Examples

Some further examples of set functions applied to the example database are given below.

##### How many rows are there in the CURRENCIES table?
``` SELECT COUNT(*)
FROM currencies;
```
##### How many currencies have a defined exchange rate (i.e. EXCHANGE_RATE is not NULL)?
``` SELECT COUNT(ALL exchange_rate)
FROM currencies;
```
##### What is the average exchange rate?
``` SELECT AVG(exchange_rate)
FROM currencies;
```

#### Decimal Calculation

The `AVG` function returns an integer if the operand is an integer, and a decimal if the operand is decimal. To force decimal calculation of averages from an integer column, cast the column operand as decimal:

``` SELECT AVG(CAST(column AS DECIMAL)) ...
``` Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se