Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


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:

 SAMPLE
 1.0
 2.0
 2.0
 2.0
 3.0
 3.0
 4.0
 5.0
 -
 -

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 BOOK_GUEST table?
 SELECT  COUNT(*) 
 FROM    BOOK_GUEST;
 
How many guests have checked out (i.e. CHECKOUT is not NULL)?
 SELECT  COUNT(ALL CHECKOUT)
 FROM    BOOK_GUEST;
What is the total bill for reservation number 1359.
 SELECT  SUM(COST)
 FROM    BILL
 WHERE   RESERVATION = 1359;
Find the average price of NO SMOKING single rooms in the hotel chain.
 SELECT  AVG(PRICE)
 FROM    ROOM_PRICES
 WHERE   ROOMTYPE IN ('NSSGLB','NSSGLS');
 

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
Mimer SQL User's Manual TOC PREV NEXT INDEX