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 available: 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. This, however, 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 require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

Examples

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

 

See also:

SQL Reference Manual, Expression (expression)

Reference Manual, Set Function (set_function_spec)

More examples for Data Query