AVG

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

numeric-expression: An expression that returns a value of any built-in numeric, character-string, or graphic-string data type. If the argument is a character-string or graphic-string, it is cast to DECFLOAT(34) before evaluating the function. The sum of the argument values must be within the range of the data type of the result.

The data type of the result is the same as the data type of the argument values, except that:

The result is DECFLOAT(34) if the argument values are DECFLOAT(16).
The result is double-precision floating point if the argument values are single-precision floating point.
The result is large integer if the argument values are small integers.
The result is decimal if the argument values are decimal or nonzero scale binary with precision p and scale s. The precision of the result is p-s+ min(ms, mp-p+s). The scale of the result is min(ms, mp-p+s).

For information about the values of p, s, ms, and mp, see Decimal arithmetic in SQL.

The function is applied to the set of values derived from the argument values by the elimination of null values. If DISTINCT is used, duplicate values are eliminated.

The result can be null. If set of values is empty, the result is the null value. Otherwise, the result is the average value of the set.

The order in which the values are aggregated is undefined, but every intermediate result must be within the range of the result data type.

If the type of the result is integer, the fractional part of the average is lost.

Notes

Results involving DECFLOAT special values: If the data type of the argument is decimal floating-point and a special value of sNaN or -sNaN, or both +Infinity and -Infinity are included in the aggregation, an error or warning is returned. Otherwise, if +NaN or -NaN is found, the result is +NaN or -NaN. If +Infinity or -Infinity is found, the result is +Infinity or -Infinity.

Examples

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).
Using the PROJECT table, set the host variable ANY_CALC to the average of each unique staffing 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).