DB2 Version 10.1 for Linux, UNIX, and Windows

AVG aggregate function

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


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

The schema is SYSIBM.

expression: An expression that returns a value of any built-in numeric data type.

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.
The result is DECFLOAT(34) if the argument is DECFLOAT(n).
The result is decimal with precision 31 and scale 31-p+s if the argument values are decimal numbers with precision -p and scale -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 eliminated. When interpreting the DISTINCT clause for decimal floating-point values that are numerically equal, the number of significant digits in the value is not considered. For example, the decimal floating-point number 123.00 is not distinct from the decimal floating-point number 123. The representation of the number returned from the query will be any one of the representations encountered (for example, either 123.00 or 123).

The result can be null. 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.

The order in which the values are added 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.

Examples

Example 1: 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.
Example 2: 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.