Oracle8i SQL Reference
Release 2 (8.1.6)

A76989-01

Library

Product

Contents

Index

Prev Up Next

Functions, 88 of 121


STDDEV

Syntax


For information on syntax and semantics, see "Analytic Functions".

Purpose

STDDEV returns sample standard deviation of expr, a set of numbers. You can use it as both an aggregate and analytic function. It differs from STDDEV_SAMP in that STDDEV returns zero when it has only 1 row of input data, whereas STDDEV_SAMP returns a null.

Oracle calculates the standard deviation as the square root of the variance defined for the VARIANCE aggregate function.

If you specify DISTINCT, you can specify only the query_partition_clause of the analytic_clause. The ORDER_BY_clause and windowing_clause are not allowed.

See Also:

"Aggregate Functions", "VARIANCE", and "STDDEV_SAMP"

Aggregate Example

SELECT STDDEV(sal) "Deviation"
   FROM emp;
 
 Deviation
----------
1182.50322

Analytic Example

The query in the following example returns the cumulative standard deviation of salary values in Department 30 ordered by hiredate:

SELECT ENAME, SAL, STDDEV(SAL) OVER (ORDER BY HIREDATE) 
   FROM EMP  
   WHERE DEPTNO=30; 
 
ENAME      SAL        STDDEV(SAL 
---------- ---------- ---------- 
ALLEN            1600          0 
WARD             1250 247.487373 
BLAKE            2850 841.130192 
TURNER           1500 715.308791 
MARTIN           1250 666.520817 
JAMES             950 668.331255 

Prev Up Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index