VAR_POP or VARIANCE or VAR aggregate function

The VAR_POP or VARIANCE or VAR function returns the biased variance (division by n) of a set of numbers.

The schema is SYSIBM.

The formula that is used to calculate the result is logically equivalent to:

VAR_POP = SUM(X**2)/COUNT(X) - (SUM(X)/COUNT(X))**2

Two asterisks (**) represent exponentiation. The exponentiation operator is not supported by Db2 for z/OS®.

The argument values can be of any built-in numeric type, and their sum must be within the range of the data type of the result. Before the function is applied to the set of values derived from the argument values, null values are eliminated. If DISTINCT is specified, redundant duplicate values are also eliminated.

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

If the argument is DECFLOAT(n), the result of the function is DECFLOAT(34). Otherwise, the result of the function is double precision floating-point.

The result can be null; if any argument is null, the result is the null value.

Otherwise, the result is the population variance of the values in 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.

Notes

Syntax alternatives:: VAR_POP should be used for conformance to the SQL standard.

Example

Using sample table DSN8D10.EMP, set host variable VARNCE, which is defined as double precision floating-point, to the population variance of the salaries (SALARY) for those employees in department (WORKDEPT) 'A00'.

   SELECT VAR_POP(SALARY)
     INTO :VARNCE
     FROM DSN8D10.EMP
     WHERE WORKDEPT = 'A00';

The value of VARNCE is set to 94915000.