Two departments can have the same average salary while one pays everyone about the same and the other pays a few people far more. The standard deviation measures that spread, in the same unit as the data. Oracle has three versions: STDDEV, STDDEV_SAMP for a sample, and STDDEV_POP for a whole population.
Code for This Guide
The main examples are in the examples/aggregate-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
Syntax:
stddev([distinct | all] expr) stddev_samp(expr) stddev_pop(expr)
| Function | Divides by | One row |
|---|---|---|
| STDDEV_POP | n: the data is the whole population | 0 |
| STDDEV_SAMP | n - 1: the data is a sample of a larger population | NULL |
| STDDEV | n - 1, like STDDEV_SAMP | 0 |
Three Versions Side by Side
This query computes the variance and standard deviation of salaries in department 30 with all three variants.
Example:
select round(variance(salary)) as variance,
round(var_pop(salary)) as var_pop,
round(var_samp(salary)) as var_samp,
round(stddev(salary), 1) as stddev,
round(stddev_pop(salary), 1) as stddev_pop,
round(stddev_samp(salary), 1) as stddev_samp
from employees
where department_id = 30;Output:
VARIANCE VAR_POP VAR_SAMP STDDEV STDDEV_POP STDDEV_SAMP ___________ ___________ ___________ _________ _____________ ______________ 22622909 20566281 22622909 4756.4 4535 4756.4
STDDEV and STDDEV_SAMP agree at 4,756.4, while STDDEV_POP, dividing by n instead of n - 1, gives the smaller 4,535. The standard deviation is the square root of the variance.
A Single Row
The only difference between STDDEV and STDDEV_SAMP is a group with one row, where the sample deviation is undefined.
Example:
-- with a single row, VARIANCE and STDDEV return 0, the _SAMP versions NULL
select variance(salary) as variance, var_samp(salary) as var_samp,
stddev(salary) as stddev, stddev_samp(salary) as stddev_samp
from employees
where employee_id = 100;Output:
VARIANCE VAR_SAMP STDDEV STDDEV_SAMP
___________ ___________ _________ ______________
0 0STDDEV returns 0, STDDEV_SAMP NULL. Use STDDEV_SAMP when one row should mean "not enough data".
Compare the Spread of Departments
Example:
select department_id, count(*) as staff, round(avg(salary)) as avg_salary,
round(stddev(salary)) as stddev_salary, round(variance(salary)) as variance_salary
from employees
where department_id in (20, 30, 40, 50)
group by department_id
order by department_id;Output:
DEPARTMENT_ID STAFF AVG_SALARY STDDEV_SALARY VARIANCE_SALARY
________________ ________ _____________ ________________ __________________
20 12 17817 5155 26570606
30 11 9009 4756 22622909
40 10 14210 8158 66547667
50 4 16400 11543 133246667Departments 20 and 50 have similar averages, but the standard deviation of department 50 is more than twice as large: its salaries are far more uneven.
Related Guides
Conclusion
STDDEV measures how spread out the values of a group are, in their own unit. Use STDDEV_POP for a whole population, STDDEV_SAMP for a sample, and remember that STDDEV is the sample version that returns 0 rather than NULL for a single row.
