VARIANCE or VARIANCE_SAMP

The VARIANCE function returns the biased variance (/n) of a set of numbers. The VARIANCE_SAMP function returns the sample variance (/n-1) of a set of numbers.


                       .-ALL------.                       
>>-+-VARIANCE------+-(-+----------+-numeric-expression)--------><
   '-VARIANCE_SAMP-'   '-DISTINCT-'

The schema is SYSIBM.

The function returns the biased variance (/n) or the sample variance (/n-1) of a set of numbers, depending on which keyword is specified.

VARIANCE

The formula that is used to calculate the biased variance is logically equivalent to:

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

VARIANCE_SAMP

The formula that is used to calculate the sample variance is logically equivalent to:

VARIANCE_SAMP = (SUM(X**2) - ((SUM(X)**2) / (COUNT(*)))) / (COUNT(*) - 1)

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.

Start of change 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). End of change

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

Alternative syntax and synonyms:

VAR or VAR_POP can be specified as synonym for VARIANCE
VAR_SAMP can be specified as a synonym for VARIANCE_SAMP

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

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

The result in VARNCE is set to a double precision-floating point number with an approximate value of '94915000.00'.

If VARIANCE_SAMP had been specified to find the sample variance of the salaries, the result in VARNCE would be set to a double precision-floating point number with an approximate value of '94915000.00'.