7.22.2 AVG
AVG determines the average.
- Organization of this subsection
(1) Specification format
general-set-function-AVG ::= {AVG([ALL] value-expression)|AVG(DISTINCT value-expression)}
(2) Explanation of specification format
- AVG([ALL] value-expression):
-
Determines the average of the results of the value expression. For details about value expressions, see 7.20 Value expression.
ALL can be omitted. The results will be the same regardless of whether it is specified.
- AVG(DISTINCT value-expression):
-
Determines the average of the results of the value expression. For details about value expressions, see 7.20 Value expression.
Duplicate values are only counted once. For example, if the values in the result of the value expression are 2, 3, 2, and 4, the execution result will be (2 + 3 + 4) ÷ 3 = 3.
(3) Rules
-
Null values are not included in the calculation.
-
When calculating the average, any digits following the significant digits are truncated.
-
In the following cases, the execution result will be a null value.
-
If the number of input rows is 0
-
If the values to be calculated are all null values
-
-
The following table shows the data type that can be specified in the value expression and the data type of the execution result of the general set function AVG.
Table 7‒16: Data type that can be specified in the value expression and data type of the execution result of the general set function AVG No.
Data type that can be specified in the value expression
Data type of the execution result of general set function AVG
1
INTEGER
INTEGER
2
SMALLINT
3
DECIMAL(m,n)
DECIMAL(38,38-m+n)
4
DOUBLE PRECISION
DOUBLE PRECISION
-
If an overflow occurs during the computation of any set function, an overflow error is generated.
(4) Example
- Example
-
Using the data in the employee table (EMPLIST), this example determines the average employee age (AGE) in each section (SCODE).
SELECT "SCODE",AVG("AGE") AS "AVG-AGE" FROM "EMPLIST" GROUP BY "SCODE"
Example of execution results