Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL STDDEV Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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



Related Links:

Related Code Snippets:
  • STDDEV - Returns the standard deviation. it returns STDDEV_SAMP if the number of pairs is more tha...
  • STDDEV_POP - STDDEV_POP computes the population standard deviation and returns the square root of ...
  • STDDEV_SAMP - STDDEV_SAMP computes the cumulative sample standard deviation and returns the square...
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 56 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?