COVAR_SAMP or COVARIANCE_SAMP

The COVARIANCE_SAMP function returns the unbiased sample covariance (n-1) of a set of number pairs.

expression1: An expression that returns a value of any built-in numeric, character-string, or graphic-string data type. If the argument is a character-string or graphic-string, it is cast to DECFLOAT(34) before evaluating the function.
expression2: An expression that returns a value of any built-in numeric, character-string, or graphic-string data type. If the argument is a character-string or graphic-string, it is cast to DECFLOAT(34) before evaluating the function.

If either argument is decimal floating-point, the result of the function is DECFLOAT(34). Otherwise, the result of the function 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 or a set with only one row, the result is a null value. Otherwise, the result is the sample covariance for the value pairs in the set. The result is equivalent to the following:

Let avgexp1 be the result of AVG(expression1) and let avgexp2 be the result of AVG(expression2).

The result of COVARIANCE_SAMP(expression1, expression2) is:

SUM( (expression1 - avgexp1) * (expression2 - avgexp2) ) / 
      ( COUNT(expression1) – 1 )

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

Notes®

Results involving DECFLOAT special values: If the data type of the argument is decimal floating-point and a special value of sNaN or -sNaN, or both +Infinity and -Infinity are included in the aggregation, an error or warning is returned. Otherwise, if +NaN or -NaN is found, the result is +NaN or -NaN. If +Infinity or -Infinity is found, the result is +Infinity or -Infinity.

Syntax alternatives: COVAR_SAMP should be used for conformance to the SQL standard.

Example

Set the host variable COVARNCE_S to the sample covariance between the salary and bonus for those employees in department 'A00' of the EMPLOYEE table. The data type of the host variable COVARNCE_S is double-precision floating point.
```
SELECT COVARIANCE_SAMP(SALARY, BONUS)
  INTO :COVARNCE_S
  FROM EMPLOYEE
  WHERE WORKDEPT = 'A00'
```
COVARNCE_S is set to 2178750.0000 when the sample table is used.