VARIANCE
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.
CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER(7, 2),
DEPTNO NUMBER(2));
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>