AVG aggregate function

The AVG function returns the average of a set of numbers.

The schema is SYSIBM.

expression: An expression that returns a set of built-in numeric or Boolean values. The AVG function ignores any null values the input expression might contain.
ALL or DISTINCT: If ALL is specified, all values returned by the expression, including duplicate values, are used to calculate the average (this is the default). If DISTINCT is specified, duplicate values are ignored. Decimal floating-point values that are numerically equal are treated as duplicates even if they have different numbers of significant digits. For example, if the set of values returned by an expression includes the decimal floating-point numbers 123, 123.0, and 123.00, only one of these values is used to calculate the average.

Result

The data type of the result is the same as the data type of the input expression, with the following exceptions:

If the data type of the input expression is SMALLINT, the data type of the result is INTEGER.
If the data type of the input expression is BOOLEAN, the data type of the result is BIGINT. The result is 1 only if all values returned by the input expression are also 1; otherwise, the result is 0.
If the data type of the input expression is single-precision floating point (REAL), the data type of the result is double-precision floating point (DOUBLE).
If the data type of the input expression is DECFLOAT(n), the data type of the result is DECFLOAT(34).

If the input expression is a DECIMAL value with precision p and scale s, the result is a DECIMAL with precision and scale as follows:

Table 1. Result precision and scale of the AVG aggregate function with DECIMAL input
DECIMAL arithmetic mode ¹	`p`	Result precision	Result scale
default	n/a	31	`31-p+s`
DEC15	<=15	15	`15-p+s`
DEC15	>15	31	`MAX(0,28-p+s)`
DEC31	n/a	31	`MAX(0,28-p+s)`

Note:

These modes are determined by the dec_arithmetic configuration parameter.

If the data type of the result is SMALLINT, INTEGER, or BIGINT, the fractional part of the average is truncated. It is not rounded up.

During evaluation, the order in which the input values are added together is undefined, but every intermediate result must be within the range of the data type of the result.

The result can be null. If the function is applied to an empty set, the result is a null value; otherwise, the result is the average value of the set.

Examples

Example 1: Using the PROJECT table, set the host variable AVERAGE (decimal(5,2)) to the average staffing level (PRSTAFF) of projects in department (DEPTNO) 'D11'.
```
   SELECT AVG(PRSTAFF)
     INTO :AVERAGE
     FROM PROJECT
     WHERE DEPTNO = 'D11'
```
Results in AVERAGE being set to 4.25 (that is 17/4) when using the sample table.
Example 2: Using the PROJECT table, set the host variable ANY_CALC (decimal(5,2)) to the average of each unique staffing level value (PRSTAFF) of projects in department (DEPTNO) 'D11'.
```
   SELECT AVG(DISTINCT PRSTAFF)
     INTO :ANY_CALC
     FROM PROJECT
     WHERE DEPTNO = 'D11'
```
Results in ANY_CALC being set to 4.66 (that is 14/3) when using the sample table.