STDDEV
STDDEV(x) returns standard deviation of x.
Standard deviation is defined as the square root of the variance. STDDEV operates on a group of rows and return one row of output.
Null values are ignored by STDDEV function.
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 stddev(sal) from emp;
STDDEV(SAL)
-----------
871.238669
SQL>
Using the STDDEV function with the group by clause:
SQL> select deptno, stddev(sal) from emp group by deptno;
DEPTNO STDDEV(SAL)
---------- -----------
30 666.520817
20 1098.74455
10 106.066017
SQL>