Set Functions

The database system contains functions that can be used column by column across several rows. These functions are known as set functions. The following set functions are possible: AVG, COUNT, MAX, MIN, STDDEV, SUM and VARIANCE.

Set functions operate across groups of numbers but return only one value. The result therefore comprises one row. If a set function is used in a data query, a similar function must also be applied to each of the other columns in the query. However, this does not apply to columns that were grouped using GROUP BY. In this case, the value of the set function can be defined for each group (see also: Creating Groups: GROUP BY, HAVING).

With the exception of the COUNT(*) function, no NULL values are included in the calculation of a set function.

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

SELECT COUNT(*) number

FROM hotel.customer

Number of customers

Result

NUMBER

15

SELECT COUNT(DISTINCT name) number_city

FROM hotel.city

Number of different cities/places

Result

NUMBER_CITY

21

SELECT SUM(price) sum_price, MIN(price) min_price, FIXED(AVG(price),5,2) avg_price, MAX(price) max_price, COUNT(*) number_room

FROM hotel.room

WHERE type = 'single'

Evaluating the prices of single rooms: sum of all prices, minimal price, average price, maximum price, and number of single rooms

Result

`SUM_PRICE`	`MIN_PRICE`	`AVG_PRICE`	`MAX_PRICE`	`NUMBER_ROOM`
`1450`	`45`	`96.67`	`160`	`15`

More Information

SQL Reference Manual, Expression (expression)

SQL Reference Manual, Set Function (set_function_spec)

More examples for Data Query