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

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

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