COVARIANCE aggregate function

The COVARIANCE function returns the (population) covariance of a set of number pairs.

The schema is SYSIBM.

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

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

The function is applied to the set of (expression1,expression2) pairs derived from the argument values by the elimination of all pairs for which either expression1 or expression2 is null.

If the function is applied to an empty set, the result is a null value. Otherwise, the result is the covariance of the value pairs in the set.

The calculation that is used to determine the biased covariance is logically equivalent to the following formula:

COVARIANCE = SUM( 
   ( expression1 - AVG(expression1) ) * 
   ( expression2 - AVG(expression2) ) ) / COUNT(expression1)

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

COVAR or COVAR_POP can be specified in place of COVARIANCE.

Example

Set the host variable COVARNCE to the covariance between salary and bonus for those employees in department 'A00' in the EMPLOYEE table. The data type of the host variable COVARNCE is double-precision floating point.

   SELECT COVARIANCE(SALARY, BONUS)
     INTO :COVARNCE
     FROM EMPLOYEE
     WHERE WORKDEPT = 'A00'

COVARNCE is set to approximately 1.68888888888889E+006 when using the sample table.

The following result set is shown for reference.

SELECT SALARY, BONUS FROM EMPLOYEE WHERE WORKDEPT = 'A00'

SALARY      BONUS      
----------- -----------
  152750.00     1000.00
   66500.00      900.00
   49250.00      600.00
   46500.00     1000.00
   39250.00      600.00

5 record(s) selected.