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

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

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se |