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.

PSOUG LOGIN
Username:
Password: