Oracle / PLSQL: STDDEV Function

This article is written about how to use the Oracle/PLSQL STDDEV characteristic with syntax and examples.

Description

The Oracle/PLSQL STDDEV characteristic returns the standard deviation of a set of numbers.

The STDDEV feature can be used two ways – as an Aggregate feature or as an Analytic function.

STDDEV Function Syntax #1 – Used as an Aggregate Function

The syntax for the STDDEV characteristic when used as an Aggregate characteristic 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 feature 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 appear at some Oracle STDDEV characteristic 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 preferred deviation of the bonus area in the employees table.

STDDEV Function Syntax #2 – Used as an Analytic Function

The syntax for the STDDEV function when used as an Analytic characteristic 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 variations 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 assertion above would return the cumulative trendy deviation of the bonuses in the Marketing department in the employees table, ordered through salary.