VARIANCE

Syntax

variance::=

Text description of variance

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

If you specify DISTINCT, then you can specify only the query_partition_clause of the analytic_clause. The order_by_clause and windowing_clause are not allowed.

Aggregate Example

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

SELECT VARIANCE(salary) "Variance"
   FROM employees;

  Variance
----------
15283140.5

Analytic Example

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

SELECT last_name, salary, VARIANCE(salary) 
      OVER (ORDER BY hire_date) "Variance"
   FROM employees 
   WHERE department_id = 30; 

LAST_NAME           SALARY   Variance
--------------- ---------- ----------
Raphaely             11000          0
Khoo                  3100   31205000
Tobias                2800 21623333.3
Baida                 2900 16283333.3
Himuro                2600   13317000
Colmenares            2500   11307000