STDDEV is one of the vital Analytic functions of Oracle. It is used to get the standard deviation of a set of numbers. The STDDEV function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i and Oracle 8i. It can be used both as an analytic function and as an aggregate function.
Syntax 1: To use as an Aggregate function.
stddev( [ DISTINCT | ALL ] expression )
Syntax 2: To use as an Analytic function.
STDDEV( [ DISTINCT | ALL ] expression ) [ OVER ( analytical_clause ) ]
Parameters: expression: It is used to specify a numeric value or a formula.
Example 1: Using STDDEV function as an Aggregate function.
select STDDEV(marks) from students; |
Explanation: Here, we are using the STDDEV function as an aggregate function. The standard deviation of the marks field in the students table will be returned after the execution of the query.
Example 2: Using STDDEV function as an Analytic function.
select name, marks, STDDEV(marks) OVER (ORDER BY id) from students where class = 'Biology';
Explanation: Here, we are using the STDDEV function as an analytic function. The cumulative standard deviation of the marks in the biology class in the students table ordered by id will be returned after the execution of the query.
