VARIANCE_SAMP aggregate function

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

The schema is SYSIBM.

expression

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

If the argument is DECFLOAT(n), the result is DECFLOAT(34); otherwise, the result is double-precision floating-point. The result can be null.

The function is applied to the set of values that are derived from the argument values by the elimination of null values. If DISTINCT is specified, redundant duplicate values are eliminated. When the DISTINCT clause is interpreted 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 that is returned from the query is any one of the representations encountered (for example, 123.00 or 123).

If the function is applied to an empty set or a set with only one row, the result is a null value. Otherwise, the result is the sample variance of the values in the set.

The calculation that is used to determine the sample variance is logically equivalent to the following formula:

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

The order in which the values are added is undefined, but every intermediate result must be within the range of the result data type.

VAR_SAMP can be specified in place of VARIANCE_SAMP.

Example

Set the host variable VARNCE to the sample variance of the salaries for those employees in department 'A00' of the EMPLOYEE table. The data type for the host variable VARNCE is double-precision floating point.

   SELECT VARIANCE_SAMP(SALARY)
     INTO :VARNCE
     FROM EMPLOYEE
     WHERE WORKDEPT = 'A00'

This statement results in VARNCE being set to approximately +2.19614375000000E+009 when the sample table is used.

The following result set is shown for reference.

   SELECT SALARY FROM EMPLOYEE WHERE WORKDEPT = 'A00'

SALARY     
-----------
  152750.00
   66500.00
   49250.00
   46500.00
   39250.00

  5 record(s) selected.