VARIANCE

Syntax

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

Purpose

VARIANCE returns variance of expr. You can use it as an aggregate or analytic function.

Oracle calculates the variance of expr as follows:

• 0 if the number of rows in expr = 1

• VAR_SAMP if the number of rows in expr > 1

Aggregate Example

The following example calculates the variance of all salaries in the EMP table:

SELECT VARIANCE(sal) "Variance"
     FROM emp;
 
Variance
----------
1389313.87

Analytic Example

The query returns the cumulative variance of salary values in Department 30 ordered by hiredate.

SELECT ename, sal, VARIANCE(sal) OVER (ORDER BY hiredate) 
   FROM emp 
   WHERE deptno=30; 

ENAME      SAL        VARIANCE(S 
---------- ---------- ---------- 
ALLEN            1600          0 
WARD             1250      61250 
BLAKE            2850     707500 
TURNER           1500 511666.667 
MARTIN           1250     444250 
JAMES             950 446666.667