STDDEV

Syntax

For information on syntax and semantics, see "Analytic Functions".

Purpose

STDDEV returns sample standard deviation of expr, a set of numbers. You can use it as both an aggregate and analytic function. It differs from STDDEV_SAMP in that STDDEV returns zero when it has only 1 row of input data, whereas STDDEV_SAMP returns a null.

Oracle calculates the standard deviation as the square root of the variance defined for the VARIANCE aggregate function.

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

See Also: "Aggregate Functions", "VARIANCE", and "STDDEV_SAMP".

Aggregate Example

SELECT STDDEV(sal) "Deviation"
   FROM emp;
 
 Deviation
----------
1182.50322

Analytic Example

The query in the following example returns the cumulative standard deviation of salary values in Department 30 ordered by hiredate:

SELECT ENAME, SAL, STDDEV(SAL) OVER (ORDER BY HIREDATE) 
   FROM EMP  
   WHERE DEPTNO=30; 
 
ENAME      SAL        STDDEV(SAL 
---------- ---------- ---------- 
ALLEN            1600          0 
WARD             1250 247.487373 
BLAKE            2850 841.130192 
TURNER           1500 715.308791 
MARTIN           1250 666.520817 
JAMES             950 668.331255