Oracle Aggregate Function - Oracle/PLSQL STDDEV Function

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

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.

The STDDEV function can be used as an Aggregate function or as an Analytic function.

STDDEV Function Syntax as an Aggregate Function

The syntax for the STDDEV function when used as an Aggregate function is:

stddev( [ DISTINCT | ALL ] expression )

expression is a numeric value or formula.

STDDEV Function Syntax as an Analytic Function

The syntax for the STDDEV function when used as an Analytic function is:

STDDEV( [ DISTINCT | ALL ] expression ) [ OVER ( analytical_clause ) ]

The SQL statement returns the cumulative standard deviation of the bonuses, ordered by salary.

select employee_name, bonus,
 STDDEV(bonus) OVER (ORDER BY salary)
 from employees;

Example


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