VAR_SAMP or VARIANCE_SAMP aggregate function

The VAR_SAMP or VARIANCE_SAMP function returns the sample variance (division by n-1) of a set of numbers.

The schema is SYSIBM.

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

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

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

The following restrictions apply to VAR_SAMP:

VAR_SAMP cannot be used as part of an OLAP specification.

Notes

Syntax alternatives:: VAR_SAMP 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 sample variance of the salaries (SALARY) for those employees in department (WORKDEPT) 'A00'.

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

The value of VARNCE is set to 1186437500.