Term: STDDEV

Definition:
In Oracle PL/SQL, STDDEV is a built in function which returns the standard deviation of a set of numbers, i.e. the square root of the variance for the input number set. It can be used as both an Aggregate and an Analytic function.

Standard deviation is a widely used measurement of variability or diversity used in statistics and probability theory. It shows how much variation or "dispersion" there is from the "average" (mean, or expected/budgeted value). A low standard deviation indicates that the data points tend to be very close to the mean, whereas high standard deviation indicates that the data are spread out over a large range of values.

Note that STDDEV returns zero for input set which contains only one element.

Example Syntax:

Aggregate Function:

STDDEV( [ DISTINCT | ALL ] expression )

Analytic Function:

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

Example Usage:

The SQL statement below returns the standard deviation of the Quantity field from ORDERS table.

SELECT STDDEV(QTY)

FROM ORDERS;



STDDEV(QTY)

-----------

 155.441001

The SQL statement below returns the cumulative standard deviation of the quantity over the orders against each date.

SELECT DT,STDDEV(QTY) OVER (ORDER_BY dt)

FROM ORDERS



DT        STDDEV(QTY)OVER(ORDERBYDT)

--------- --------------------------

20-OCT-10                          0

01-NOV-10                 106.066017

09-DEC-10                 76.3762616

13-JAN-11                 159.373775

15-JAN-11                 164.468842

16-JAN-11                 159.342399

18-JAN-11                 155.441001