VAR_POP

Syntax

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

Purpose

VAR_POP returns the population variance of a set of numbers after discarding the nulls in this set. You can use it as both an aggregate and analytic function.

The expr is a number expression, and the function returns a value of type NUMBER. If the function is applied to an empty set, it returns null. The function makes the following calculation:

(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)

See Also: "Aggregate Functions"

Aggregate Example

The following example returns the population variance of the salaries in the EMP table:

SELECT VAR_POP(sal) FROM emp;

VAR_POP(SAL)
------------
  1298434.31

Analytic Example

The following example calculates the cumulative population and sample variances of the monthly sales in 1998:

SELECT s_month, VAR_POP(SUM(s_amount)) OVER (ORDER BY s_month),
   VAR_SAMP(SUM(s_amount)) OVER (ORDER BY s_month) 
   FROM sales
   WHERE s_year =1998
   GROUP BY s_month;

S_MONTH    VAR_POP(SU VAR_SAMP(S
---------- ---------- ----------
         6          0           
         8  440588496  881176992
        11  538819892  808229838