AVG aggregate function

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

The schema is SYSIBM.

The argument values can be of any built-in numeric data type, and their sum must be within the range of the data type of the result.

The arguments can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34).

The data type of the result is determined as follows:

DECFLOAT(34) if the argument is DECFLOAT(n).
Large integer if the argument is small integer.
Double precision floating-point if the argument is single precision floating-point.
Otherwise, the result is the same as the data type of the argument.

The result can be null.

If the data type of the argument values is decimal with precision p and scale s, the precision (P) and scale (S) of the result depend on p and the decimal precision option:

If p is greater than 15 or the DEC31 option is in effect, P is 31 and S is max(0,28-p+s).
Otherwise, P is 15 and S is 15-p+s.

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

If the function is applied to an empty set, the result is the null value. Otherwise, the result is the average value of the set. The order in which the summation part of the operation is performed 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.

Example: Assuming DEC15, set the DECIMAL(15,2) variable AVERAGE to the average salary in department D11 of the employees in the sample table DSN8C10.EMP.

   EXEC SQL SELECT AVG(SALARY)
     INTO :AVERAGE
     FROM DSN8C10.EMP
     WHERE WORKDEPT = 'D11';