Oracle/PLSQL: StdDev Function

In Oracle/PLSQL, the 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.

Syntax #1 - Used as an Aggregate Function

The syntax for the stddev function when used as an Aggregate function is:

stddev( [ DISTINCT | ALL ] expression )

Applies To

• Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

For Example

select stddev(bonus)

from employees;

The SQL statement above would return the standard deviation of the bonus field in the employees table.

Syntax #2 - Used as an Analytic Function

The syntax for the cume_dist function when used as an Analytic function is:

stddev( [ DISTINCT | ALL ] expression ) [ OVER ( analytical_clause ) ]

Applies To

• Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

For Example

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.