The AVG function returns the average of a set of numbers.
The data type of the result is the same as the data type of the argument values, except that:
For information on 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.
SELECT AVG(PRSTAFF) INTO :AVERAGE FROM PROJECT WHERE DEPTNO = 'D11'Results in AVERAGE being set to 4.25 (that is, 17/4).
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).
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.