Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
Syntax
Purpose
STDDEV
returns the 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 null.
Oracle Database calculates the standard deviation as the square root of the variance defined for the VARIANCE
aggregate function.
This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.
If you specify DISTINCT
, then you can specify only the query_partition_clause
of the analytic_clause
. The order_by_clause
and windowing_clause
are not allowed.
Aggregate Examples
The following example returns the standard deviation of the salaries in the sample hr.employees
table:
SELECT STDDEV(salary) "Deviation" FROM employees; Deviation ---------- 3909.36575
Analytic Examples
The query in the following example returns the cumulative standard deviation of the salaries 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