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

Term: STDDEV_SAMP

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

Note that STDDEV_SAMP returns NULL for input set which contains only one element.

Example Syntax:

Aggregate Function:

STDDEV_SAMP( [ DISTINCT | ALL ] expression )


Analytic Function:

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


Example Usage:

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

SELECT STDDEV_SAMP(QTY)
FROM ORDERS;

STDDEV_SAMP(QTY)
----------------
155.441001


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

SELECT DT,STDDEV_SAMP(QTY) OVER (ORDER BY DT)
FROM ORDERS


DT STDDEV_SAMP(QTY)OVER(ORDERBYDT)
--------- -------------------------------
20-OCT-10
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

7 rows selected.



Related Links:

Related Code Snippets:
  • STDDEV_SAMP - STDDEV_SAMP computes the cumulative sample standard deviation and returns the square...
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 146 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?