Home Privacy Policy Feedback Link to us Site Map Forums

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 8i, Oracle 9i, Oracle 10g, Oracle 11g

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 8i, Oracle 9i, Oracle 10g, Oracle 11g

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.