STDDEV_SAMP

Syntax

stddev_samp::=

Text description of stddev_samp

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.

Aggregate Example

The following example returns the population and sample standard deviations of the amount of sales in the sample table sh.sales:

SELECT STDDEV_POP(amount_sold) "Pop", 
   STDDEV_SAMP(amount_sold) "Samp"
   FROM sales;

       Pop       Samp
---------- ----------
896.355151 896.355592

Analytic Example

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

SELECT department_id, last_name, hire_date, salary, 
   STDDEV_SAMP(salary) OVER (PARTITION BY department_id 
      ORDER BY hire_date 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev 
   FROM employees;

DEPARTMENT_ID LAST_NAME       HIRE_DATE     SALARY   CUM_SDEV
------------- --------------- --------- ---------- ----------
           10 Whalen          17-SEP-87       4400
           20 Hartstein       17-FEB-96      13000
           20 Goyal           17-AUG-97       6000 4949.74747
           30 Raphaely        07-DEC-94      11000
           30 Khoo            18-MAY-95       3100 5586.14357
           30 Tobias          24-JUL-97       2800  4650.0896
           30 Baida           24-DEC-97       2900 4035.26125
.
.
.
          100 Chen            28-SEP-97       8200 2003.33056
          100 Sciarra         30-SEP-97       7700 1925.91969
          100 Urman           07-MAR-98       7800 1785.49713
          100 Popp            07-DEC-99       6900 1801.11077
          110 Higgens         07-JUN-94      12000
          110 Gietz           07-JUN-94       8300 2616.29509