ORACLE STDDEV

STDDEV is an Oracle Function that is classified as both an aggregate and analytical function.

Standard Deviation in Statistics is a term used to define the dispersion of values from the mean or how spread out the values are. Oracle calculates Standard deviation as the square root of VARIANCE function.

Let’s see some examples to understand this better:

For these examples, we will be using the Employee table from the HR schema

STDDEV as an Aggregate Function Example

Syntax

SELECT STDDEV( [ DISTINCT | ALL ] expression )
FROM TABLE;

Here,

expression = It could be a set of values or a column name. The datatype is numeric or something we can convert to numeric.

Let’s see one query where we find the standard deviation of salary in employees table and we round off the results to 2 digits after decimal point.

SELECT ROUND(STDDEV(salary),2)
FROM employees;

Result:

The query returns the standard deviation of salary in employees table.

2. STDDEV as an Analytical Function Example

Syntax

SELECT STDDEV( [ DISTINCT | ALL ] expression ) [ OVER ( analytical_expression ) ]
FROM table;

Here,

expression – It is a column name or a set of values from which Oracle calculates Standard deviation.

Let’s see one query to calculate cumulative standard deviation in salary with respect to hire date of employees.

SELECT first_name, last_name, hire_date, salary,
STDDEV(salary) OVER (ORDER BY hire_date) "Standard Deviation"
FROM employees
WHERE department_id = 100;

Result:

The query returns the cumulative standard deviation in salary.

In this article, we learned how to use Standard Deviation both as an aggregate and analytical function.