COVAR_SAMP or COVARIANCE_SAMP aggregate function

The schema is SYSIBM.

The argument values must each be the value of any built-in numeric data type.

If an 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.

The function is applied to the set of (expression-1, expression-2) pairs that are derived from the argument values by the elimination of all pairs for which either expression-1 or expression-2 is null.

If the function is applied to an empty set, the result is a null value. Otherwise, the result is the sample covariance of the value pairs in the set. The result is equivalent to the following output:

1. Let samp_avgexp1 be the result of SUM(expression-1)/n-1 and let samp_avgexp2 be the result of SUM(expression-2)/n-1.
2. The result of COVAR_SAMP(expression-1, expression-2) is AVG((expression-1 - samp_avgexp1) * (expression-2 - samp_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.

The following restrictions apply to COVAR_SAMP:

• COVAR_SAMP cannot be used as part of an OLAP specification.

   SELECT COVAR_SAMP(SALARY, BONUS)
     INTO :COVARNCE
     FROM DSN8C10.EMP
     WHERE WORKDEPT = 'A00';