STDDEV_SAMP

Purpose

This function returns the standard deviation of expr within a window or group of rows. Therefore, it has the following formula:

Syntax

stddev_samp::=

Usage Notes

  • STDDEV_SAMP is identical to the STDDEV function. However, if a window or group has only one row, then the result of this function is NULL instead of 0.
  • With DISTINCT, the function considers duplicate values of expr only once. It considers all occurrences with ALL. If the function contains neither ALL nor DISTINCT, the default is ALL.
  • If ALL or nothing is specified, then all of the entries are considered. If DISTINCT is specified, duplicate entries are only accounted for once.
  • For the OVER() clause and analytic functions in general, refer to the Analytic Functions section.

Examples

Aggregate Function

SELECT 
    department, 
   STDDEV_SAMP(current_salary) STDDEV_SAMP
FROM employee_table GROUP BY department;
Result
DEPARTMENT STDDEV_SAMP
ACCOUNTS 10606.602
HR 14361.407

Analytic Function

SELECT 
    id, department, hire_date, current_salary, 
    STDDEV_SAMP(current_salary) OVER (PARTITION BY department ORDER BY hire_date) STDDEV_SAMP 
FROM employee_table ORDER BY department, hire_date;
Result
ID DEPARTMENT HIRE_DATE CURRENT_SALARY STDDEV_SAMP
2005 ACCOUNTS 2013-01-01 80000 NULL
2003 ACCOUNTS 2015-07-01 80000 0
2002 ACCOUNTS 2017-01-01 65000 7500
2004 ACCOUNTS 2017-01-01 70000 7500
2001 ACCOUNTS 2018-07-01 55000 10606.602
1003 HR 2014-01-01 90000 NULL
1002 HR 2016-01-01 70000 11547.005
1004 HR 2016-01-01 70000 11547.005
1001 HR 2018-01-01 55000 14361.407