7.22.2 AVG

(1) Specification format

general-set-function-AVG ::= {AVG([ALL] value-expression)|AVG(DISTINCT value-expression)}

To Page Top

(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.

To Page Top

(3) Rules

1. Null values are not included in the calculation.

2. When calculating the average, any digits following the significant digits are truncated.

3. 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

4. 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

5. If an overflow occurs during the computation of any set function, an overflow error is generated.

To Page Top

(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

To Page Top