Specifying a set function in SQL allows you to calculate the average, sum, maximum value, minimum value, and the number of rows, as well as to concatenate XML type values.
For details about the XMLAGG set function, see 1.14.5(1) XMLAGG.
The following table lists the functions of the set functions.
Table 2-24 Functions of set functions
Item | Set function | ||||||
---|---|---|---|---|---|---|---|
AVG | SUM | MAX | MIN | COUNT | COUNT_FLOAT | XMLAGG | |
Function | Calculates an average. | Calculates a sum. | Calculates a maximum value. | Calculates a minimum value. | Calculates the number of rows. | Calculates the number of rows. | Joins XML type values. |
Treatment of null value | Ignored | Ignored | Ignored | Ignored | Ignored#1 | Ignored#1 | Ignored |
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 after removing rows that contain an identical value in specified value expressions. | Number of rows obtained by removing rows that contain a duplicated value in specified columns. | Cannot be specified. |
Function value for a set in which the target of application is either zero#3 or only the null value | Null value | Null value | Null value | Null value | 0 | 0 | Null value |
Whether a repetition column can be specified in an argument#2 | Cannot be specified. | Cannot be specified. | Can be specified. | Can be specified. | Can be specified. | Cannot be specified. | Can be specified. |
#1: A null value is ignored in most set functions, except for COUNT(*) and COUNT_FLOAT(*), where the total number of rows that satisfy the conditions is calculated 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-25 Relationships between data types of columns and data types of function values
Data type of set function argument | AVG | SUM | MAX and MIN | COUNT | COUNT_FLOAT | XMLAGG |
---|---|---|---|---|---|---|
INTEGER | INTEGER | INTEGER | INTEGER | INTEGER | FLOAT | -- |
SMALLINT | INTEGER | INTEGER | SMALLINT | INTEGER | FLOAT | -- |
DECIMAL(p,s) | DECIMAL(max_prec#3, max_prec#3 - p + s) | DECIMAL(max_prec#3, s) | DECIMAL(p,s) | INTEGER | FLOAT | -- |
FLOAT | FLOAT | FLOAT | FLOAT | INTEGER | FLOAT | -- |
SMALLFLT | SMALLFLT | SMALLFLT | SMALLFLT | INTEGER | FLOAT | -- |
INTERVAL YEAR TO DAY | -- | -- | INTERVAL YEAR TO DAY | INTEGER | FLOAT | -- |
INTERVAL HOUR TO SECOND | -- | -- | INTERVAL HOUR TO SECOND | INTEGER | FLOAT | -- |
CHAR(n) | -- | -- | CHAR(n)#1 | INTEGER | FLOAT | -- |
VARCHAR(n) | -- | -- | VARCHAR(n)#1 | INTEGER | FLOAT | -- |
NCHAR(n) | -- | -- | NCHAR(n) | INTEGER | FLOAT | -- |
NVARCHAR(n) | -- | -- | NVARCHAR(n) | INTEGER | FLOAT | -- |
MCHAR(n) | -- | -- | MCHAR(n) | INTEGER | FLOAT | -- |
MVARCHAR(n) | -- | -- | MVARCHAR(n) | INTEGER | FLOAT | -- |
DATE | -- | -- | DATE | INTEGER | FLOAT | -- |
TIME | -- | -- | TIME | INTEGER | FLOAT | -- |
TIMESTAMP | -- | -- | TIMESTAMP | INTEGER | FLOAT | -- |
BINARY(n)#2 | -- | -- | BINARY(n) | INTEGER | FLOAT | -- |
BLOB(n) | -- | -- | -- | -- | -- | -- |
BOOLEAN | -- | -- | -- | -- | -- | -- |
XML | -- | -- | -- | -- | -- | XML |
Abstract data type(not including XML type) | -- | -- | -- | -- | -- | -- |
--: Cannot be used.
Table 2-26 Maximum precision value of the DECIMAL type
System common definition pd_sql_dec_op_maxprec operand | Precision p of set function argument | max_prec value |
---|---|---|
29 or omitted | p![]() | 29 |
p > 29 | 38 | |
38 | Any | 38 |
set-function::={COUNT(*)|COUNT FLOAT(*)|ALL set-function|DISTINCT set-function|XMLAGG-set-function}
ALL set-function::={AVG|SUM|MAX|MIN|COUNT|COUNT FLOAT}([ALL] {value-expression|FLAT-specification})
DISTINCT set-function::= {AVG|SUM|MAX|MIN|COUNT|COUNT FLOAT}
(DISTINCT {value-expression|FLAT-specification})
FLAT-specification:: = FLAT (column-specification)
For details about the format of the XMLAGG set function, see 1.14.5(1) XMLAGG.
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