STDDEV_SAMP

Syntax

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

Purpose

STDDEV_SAMP computes the cumulative sample standard deviation and returns the square root of the sample variance. You can use it as both an aggregate and analytic function.

The expr is a number expression, and the function returns a value of type NUMBER. This function is same as the square root of the VAR_SAMP function. When VAR_SAMP returns null, this function returns null.

See Also: "Aggregate Functions" and "VAR_SAMP".

Aggregate Example

See the example for "STDDEV_POP".

Analytic Example

The following example returns the sample standard deviation of salaries in the EMP table by department:

SELECT deptno, ename, hiredate, sal, 
   STDDEV_SAMP(sal) OVER (PARTITION BY deptno ORDER BY hiredate 
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev 
   FROM emp;

    DEPTNO ENAME      HIREDATE         SAL   CUM_SDEV
---------- ---------- --------- ---------- ----------
        10 CLARK      09-JUN-81       2450
        10 KING       17-NOV-81       5000 1803.12229
        10 MILLER     23-JAN-82       1300 1893.62967
        20 SMITH      17-DEC-80        800
        20 JONES      02-APR-81       2975 1537.95725
        20 FORD       03-DEC-81       3000 1263.01557
        20 SCOTT      19-APR-87       3000  1095.8967
        20 ADAMS      23-MAY-87       1100  1123.3321
        30 ALLEN      20-FEB-81       1600
        30 WARD       22-FEB-81       1250 247.487373
        30 BLAKE      01-MAY-81       2850 841.130192
        30 TURNER     08-SEP-81       1500 715.308791
        30 MARTIN     28-SEP-81       1250 666.520817
        30 JAMES      03-DEC-81        950 668.331255