SQL Reference

COVARIANCE

>>-+-COVARIANCE-+--(----expression1--,--expression2--)---------><
   '-COVAR------'
 

The schema is SYSIBM.

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

The argument values must be numbers.

The data type of the result is double-precision floating point. 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 result is equivalent to the following:

1. Let avgexp1 be the result of AVG(expression1) and let avgexp2 be the result of AVG(expression2).
2. The result of COVARIANCE(expression1, expression2) is AVG( (expression1 - avgexp1) * (expression2 - avgexp2 )

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

Example:

• Using the EMPLOYEE table, set the host variable COVARNCE (double precision floating point) to the covariance between salary and bonus for those employees in department (WORKDEPT) 'A00'.

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

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