Set functions enable average, sum, maximum, minimum, and number of rows computations to be made in an SQL. Table 2-20 shows the functions of the set function. Table 2-21 shows the relationships between the data type of a column and the data type of the value of a set function.
Table 2-20 Functions of set functions
Item | Set function | ||||
---|---|---|---|---|---|
AVG | SUM | MAX | MIN | COUNT | |
Function | Calculates an average. | Calculates a sum. | Calculates a maximum value. | Calculates a minimum value. | Calculates the number of rows. |
Treatment of null value | Ignored | Ignored | Ignored | Ignored | Ignored1 |
Meaning of DISTINCT specification | Average value obtained by removing rows that contain a duplicated value in specified columns. | Sum obtained by removing rows that contain a duplicated value in specified columns. | Has no meaning. | Has no meaning. | Number of rows obtained by removing rows that contain a duplicated value in specified columns. |
Function value for a set in which the target of application is either zero3 or only the null value | Null value | Null value | Null value | Null value | 0 |
Whether a repetition column can be specified in an argument2 | Cannot be specified. | Cannot be specified. | Can be specified. | Can be specified. | Cannot be specified. |
1 In general, set functions ignore the null value. However, the COUNT(*) set function counts all rows that satisfy a given set of conditions, regardless of null values.
2 With the set functions MAX and MIN, a repetition column can be specified in an argument by coding a FLAT specification. If a repetition column is specified, the set functions MAX and MIN calculate either a maximum or a minimum from all the elements in the rows within the scope of the operation. This process excludes any columns in which the values of the entire column are the null value (a repetition column in which the number of elements is 0).
3 If a GROUP BY or HAVING clause is specified, groups for which the number of rows is 0 are excluded from the calculation.
Table 2-21 Relationships between data types of columns and data types of function values
Data type of set function argument | AVG | SUM | MAX and MIN | COUNT |
---|---|---|---|---|
INTEGER | INTEGER | INTEGER | INTEGER | INTEGER |
SMALLINT | INTEGER | INTEGER | SMALLINT | INTEGER |
DECIMAL(p,s) | DECIMAL (29, 29-p+s) | DECIMAL (29,s) | DECIMAL(p,s) | INTEGER |
FLOAT | FLOAT | FLOAT | FLOAT | INTEGER |
SMALLFLT | SMALLFLT | SMALLFLT | SMALLFLT | INTEGER |
INTERVAL YEAR TO DAY | ![]() | ![]() | INTERVAL YEAR TO DAY | INTEGER |
INTERVAL HOUR TO SECOND | ![]() | ![]() | INTERVAL HOUR TO SECOND | INTEGER |
CHAR(n) | ![]() | ![]() | CHAR(n) | INTEGER |
VARCHAR(n) | ![]() | ![]() | VARCHAR(n) | INTEGER |
NCHAR(n) | ![]() | ![]() | NCHAR(n) | INTEGER |
NVARCHAR(n) | ![]() | ![]() | NVARCHAR(n) | INTEGER |
MCHAR(n) | ![]() | ![]() | MCHAR(n) | INTEGER |
MVARCHAR(n) | ![]() | ![]() | MVARCHAR(n) | INTEGER |
DATE | ![]() | ![]() | DATE | INTEGER |
TIME | ![]() | ![]() | TIME | INTEGER |
TIMESTAMP | ![]() | ![]() | TIMESTAMP | INTEGER |
BINARY(n)1 | ![]() | ![]() | BINARY(n) | INTEGER |
BLOB(n) | ![]() | ![]() | ![]() | ![]() |
BOOLEAN | ![]() | ![]() | ![]() | ![]() |
Abstract data type | ![]() | ![]() | ![]() | ![]() |
: Cannot be used.
1 n should be less than or equal to 32,000.
set-function::={COUNT(*)|ALL set-function|DISTINCT set-function}
ALL set-function::={AVG|SUM|MAX|MIN|COUNT}([ALL] {value-expression|FLAT-specification})
DISTINCT set-function::= {AVG|SUM|MAX|MIN|COUNT}
(DISTINCT {value-expression|FLAT-specification})
FLAT-specification:: = FLAT (column-specification)
SELECT AVG(SQUANTITY) FROM STOCK
SELECT N'skirt',SUM(SQUANTITY)
FROM STOCK
WHERE PNAME=N'skirt'
SELECT SUM(PRICE*SQUANTITY)
FROM STOCK
SELECT COUNT(*),MAX(SQUANTITY),MIN(SQUANTITY)
FROM STOCK