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.
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.
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 |
SQL Reference Manual, Expression (expression)
SQL Reference Manual, Set Function (set_function_spec)
More examples for Data Query