Oracle Aggregate Function - Oracle/PLSQL VARIANCE Function

This Oracle tutorial explains how to use the Oracle/PLSQL VARIANCE function.

The Oracle/PLSQL VARIANCE function returns the variance of a set of numbers.

VARIANCE(x) returns the variance of x.

Variance is equal to the square of the standard deviation. It shows the spread or variation of a group of numbers in a sample.

VARIANCE operates on a group of rows and return one row of output. Null values are ignored by VARIANCE. You can use the DISTINCT keyword to exclude duplicate entries.

Syntax

The syntax for the Oracle/PLSQL VARIANCE function is:

VARIANCE( expression )

expression is a numeric expression.

Example


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
-- from www. j  a  v  a 2  s .  co m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

SQL> SELECT VARIANCE(sal)
  2  FROM emp;

VARIANCE(SAL)
-------------
   759056.818

SQL>

Using the VARIANCE function with the group by clause:


SQL> select deptno, VARIANCE(sal) from emp group by deptno;

    DEPTNO VARIANCE(SAL)
---------- -------------
        30        444250
        20    1207239.58
        10         11250

SQL>