SQL Reference

AVG

             .-ALL------.
>>-AVG--(----+----------+--expression--)-----------------------><
             '-DISTINCT-'
 

The schema is SYSIBM.

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

The argument values must be numbers and their sum must be within the range of the data type of the result. The result can be null.

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

• The result is a large integer if the argument values are small integers.
• The result is double-precision floating point if the argument values are single-precision floating point.

If the data type of the argument values is decimal with precision p and scale s, the precision of the result is 31 and the scale is 31-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, duplicate values are eliminated.

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.

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

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) when using the sample table.

• 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.