COVAR_POP

Syntax

For information on syntax and semantics, see "Analytic Functions".

Purpose

COVAR_POP returns the population covariance of a set of number pairs. You can use it as an aggregate or analytic function.

Both expr1 and expr2 are number expressions. Oracle applies the function to the set of (expr1 , expr2) pairs after eliminating all pairs for which either expr1 or expr2 is null. Then Oracle makes the following computation:

(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n

where n is the number of (expr1 , expr2) pairs where neither expr1 nor expr2 is null.

The function returns a value of type NUMBER. If the function is applied to an empty set, it returns null.

See Also: "Aggregate Functions".

Aggregate Example

The following example calculates the population covariance for the amount of sales and sale profits for each year from the table SALES.

SELECT s_year, 
   COVAR_POP(s_amount, s_profit) AS COVAR_POP,
   COVAR_SAMP(s_amount, s_profit) AS COVAR_SAMP
   FROM sales GROUP BY s_year;

S_YEAR      COVAR_POP COVAR_SAMP
---------- ---------- ----------
      1998 3747965.53 4060295.99
      1999 360536.162 400595.736

Analytic Example

See "COVAR_SAMP".