Oracle / PLSQL: STDDEV Function
This Oracle tutorial explains how to use the Oracle/PLSQL STDDEV function with syntax and examples.
Description
The Oracle/PLSQL STDDEV function returns the standard deviation of a set of numbers.
The STDDEV function can be used two ways - as an Aggregate function or as an Analytic function.
STDDEV Function Syntax #1 - Used as an Aggregate Function
The syntax for the STDDEV function when used as an Aggregate function is:
stddev( [ DISTINCT | ALL ] expression )
Parameters or Arguments
- expression
- A numeric value or formula.
Returns
The STDDEV function returns a numeric value.
Applies To
The STDDEV function can be used in the following versions of Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Example (as an Aggregate Function)
Let's look at some Oracle STDDEV function examples and explore how to use the STDDEV function in Oracle/PLSQL.
For example:
select STDDEV(bonus) from employees;
The SQL statement above would return the standard deviation of the bonus field in the employees table.
STDDEV Function Syntax #2 - Used as an Analytic Function
The syntax for the STDDEV function when used as an Analytic function is:
STDDEV( [ DISTINCT | ALL ] expression ) [ OVER ( analytical_clause ) ]
Returns
The STDDEV function returns a numeric value.
Applies To
The STDDEV function can be used in the following versions of Oracle/PLSQL:
- Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i
Example (as an Analytic Function)
select employee_name, bonus, STDDEV(bonus) OVER (ORDER BY salary) from employees where department = 'Marketing';
The SQL statement above would return the cumulative standard deviation of the bonuses in the Marketing department in the employees table, ordered by salary.
Advertisements