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

## Set function

## 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)## About Set Functions

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.## Note: Set functions may not be used together with direct column references in the SELECT list (unless the SELECT statement includes a GROUP BY clause, see Grouped Set Functions - the GROUP BY Clause).

## Example of Set Functions

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

## Note: A hyphen - indicates NULL.

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## Note: AVG(column) is equivalent to SUM(column)/COUNT(column). However, the expression SUM(column)/COUNT(*) will give a different answer if the column includes NULL values.

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