STDDEV_SAMP

Syntax

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.

This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.

This function is same as the square root of the VAR_SAMP function. When VAR_SAMP returns null, this function returns null.

Aggregate Example

Refer to the aggregate example for STDDEV_POP.

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
   ORDER BY department_id, last_name, hire_date, salary, cum_sdev;

DEPARTMENT_ID LAST_NAME       HIRE_DATE     SALARY   CUM_SDEV
------------- --------------- --------- ---------- ----------
           10 Whalen                    17-SEP-87       4400
           20 Fay                       17-AUG-97       6000 4949.74747
           20 Hartstein                 17-FEB-96      13000
           30 Baida                     24-DEC-97       2900 4035.26125
           30 Colmenares                10-AUG-99       2500 3362.58829
           30 Himuro                    15-NOV-98       2600  3649.2465
           30 Khoo                      18-MAY-95       3100 5586.14357
           30 Raphaely                  07-DEC-94      11000
. . .
          100 Greenberg                 17-AUG-94      12000 2121.32034
          100 Popp                      07-DEC-99       6900 1801.11077
          100 Sciarra                   30-SEP-97       7700 1925.91969
          100 Urman                     07-MAR-98       7800 1785.49713
          110 Gietz                     07-JUN-94       8300 2616.29509
          110 Higgins                   07-JUN-94      12000
              Grant                     24-MAY-99       7000