# STDDEV, STDDEV_POP and STDDEV_SAMP Analytic Functions in Oracle SQL

## STDDEV, STDDEV_POP and STDDEV_SAMP Analytic Functions in Oracle SQL

Standard deviation is a number used to tell how measurements for a group are spread out from the average (mean), or expected value.
A low standard deviation means that most of the numbers are very close to the average.
A high standard deviation means that the numbers are spread out.

STDDEV : aggregate functions are used to calculate the standard deviation.
Syntax:
STDDEV( expr) [ OVER ]

STDDEV_POP: aggregate function are used to calculate the population standard deviation.
Syntax:
STDDEV_POP(expr) [ PARTITION | OVER ]

STDDEV_SAMP: aggregate function are used to calculate the cumulative sample standard deviation of a set of data respectively
Syntax:
STDDEV_SAMP(expr) [ PARTITION | OVER ]

Example of Standard deviation calculation
Calculate the population standard deviation & standard deviation:
``` 1. Consider 9 number 1 2 3 4 5 6 7 8 9 = 45```

``` 2. Find the average of 9 number: Average = 45/9 = 5 3. Do the difference of each number in the list from the mean. 4. Square the result of each difference 1-5 = -4 = (-4*-4) = 16 2-5 = -3 = (-3*-3) = 9 3-5 = -2 = (-2*-2) = 4 4-5 = -1 = (-1*-1) = 1 5-5 = 0 = (0*0) = 0 6-5 = 1 = (1*1) = 1 7-5 = 2 = (2*2) = 4 8-5 = 3 = (3*3) = 9 9-5 = 4 = (4*4) = 16 5. Find average of values: 16+9+4+1+0+1+4+9+16 = 60/9 = 6.66 = population standard deviation. 60/8 = 7.5 = sample standard deviation 6. Take square root. ```

```6.66 square root = 2.58 = population standard deviation. 7.5 square root = 2.73 = sample standard deviation Note: If n is used at step 5 then it is population standard deviation. If n-1 at 5 step is used then it is sample standard deviation. ```

Example with SQL function to calculate standard deviation
``` SQL> create table test ( id number); SQL> insert into test values(1); SQL> insert into test values(2); SQL> insert into test values(3); SQL> insert into test values(4); SQL> insert into test values(5); SQL> insert into test values(6); SQL> insert into test values(7); SQL> insert into test values(8); SQL> insert into test values(9);```

``` SELECT STDDEV(id) AS stddev, STDDEV_POP(id) AS stddev_pop, STDDEV_SAMP(id) AS stddev_samp FROM test; STDDEV STDDEV_POP STDDEV_SAMP ---------- ---------- ----------- 2.73861279 2.5819889 2.73861279 ```

Example using over clause
SELECT last_name, salary,
STDDEV(salary) OVER (ORDER BY hire_date) “StdDev”,
STDDEV_SAMP(salary) OVER ( ORDER BY hire_date ) “STDDEVSAMP”,
STDDEV_POP(salary) OVER (ORDER BY hire_date ) “STDDEVPOP”
FROM employees
WHERE department_id = 30
ORDER BY last_name, salary, “StdDev”;

```LAST_NAME      SALARY     StdDev STDDEVSAMP  STDDEVPOP
---------- ---------- ---------- ---------- ----------
Baida            2900 4705.67034 4705.67034 3842.16374
Colmenares       2500 3715.23889 3715.23889 3323.01068
Himuro           2600 4118.55557 4118.55557 3566.77375
Khoo                           0                     0
Raphaely        11000          0                     0
Tobias           2800 5798.27561 5798.27561       4100
```
This entry was posted in Oracle and tagged , , , on . 