ADABAS has some set functions that operate in columns on several rows. These functions are called briefly set functions.
The functions produce the values
SUM the sum total
MIN the minimum
AVG the average
MAX the maximum
COUNT (distinct...) the number of different values
COUNT (*) the number of all values
STDDEV the standard deviation
VARIANCE the variance
NULL values are not included in the calculation, except for COUNT (*).
The query
SELECT SUM(account) sum_account, MIN(account) min_account,
FIXED (AVG(account),7,2) avg_account,
MAX(account) max_account, COUNT(*) number
FROM customer
WHERE city = 'Los Angeles'
gives the table:
'SUM(account)' produces the sum of the values stored in the account column for all selected rows.
How many customers are there?
SELECT COUNT(*) number
FROM customer
In how many cities do these customers live?
SELECT COUNT(DISTINCT city) number_of_cities
FROM customer
How many customers who are companies are taken into account? What is the average value of their accounts?
SELECT COUNT(*) number, FIXED (AVG(account),7,2) avg_account
FROM customer
WHERE firstname IS NULL
Set functions operate on groups of numbers, but they return only one value. The result therefore consists of one row. Whenever a set function is used in a query, a set function must be applied to any other column of the query. This is not true for a column used for grouping by means of GROUP BY. In such a case, the value of the set function is determined for every group.
This is noted as:
function name (expression)
The parentheses are needed. In most cases, 'expression' is only a column name, but it can also be
- an arithmetic expression,
- any expression formed by other functions,
- a constant,
- DISTINCT with column name.