STDDEV

Syntax

stddev::=

Text description of stddev

See Also: "Analytic Functions" for information on syntax, semantics, and restrictions

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 "About SQL Expressions" for information on valid forms of expr

Aggregate Example

The following example returns the standard deviation of salary values in the sample hr.employees table:

SELECT STDDEV(salary) "Deviation"
   FROM employees;
 
 Deviation
----------
3909.36575

Analytic Example

The query in the following example returns the cumulative standard deviation of salary values in Department 80 in the sample table hr.employees, ordered by hire_date:

SELECT last_name, salary, 
   STDDEV(salary) OVER (ORDER BY hire_date) "StdDev"
   FROM employees  
   WHERE department_id = 30; 
 
LAST_NAME                     SALARY     StdDev
------------------------- ---------- ----------
Raphaely                       11000          0
Khoo                            3100 5586.14357
Tobias                          2800  4650.0896
Baida                           2900 4035.26125
Himuro                          2600  3649.2465
Colmenares                      2500 3362.58829