AVG

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

AVG-Syntax Diagram

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.

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 type of the result is integer, the fractional part of the average is lost.

Example:

The following example returns the average of values in the column SALARY in the table EMPLOYEE.


 SELECT AVG(SALARY) FROM EMPLOYEE;

The above example returns 49000.00000.