## Wednesday, November 27, 2013

### Calculate Standard Deviation Using SQL

#### Problem

How do we calculate the standard deviation of a variable, such as salary in the following table, using SQL?
```SQL> select employee_id, department_id, hire_date, salary
from EMPLOYEES where rownum <=20;

EMPLOYEE_ID DEPARTMENT_ID HIRE_DATE   SALARY
----------- ------------- --------- --------
100            90 17-JUN-87  24000.0
101            90 21-SEP-89  17000.0
102            90 13-JAN-93  17000.0
103            60 03-JAN-90   9000.0
104            60 21-MAY-91   6000.0
105            60 25-JUN-97   4800.0
106            60 05-FEB-98   4800.0
107            60 07-FEB-99   4200.0
108           100 17-AUG-94  12000.0
109           100 16-AUG-94   9000.0
110           100 28-SEP-97   8200.0
111           100 30-SEP-97   7700.0
112           100 07-MAR-98   7800.0
113           100 07-DEC-99   6900.0
114            30 07-DEC-94  11000.0
115            30 18-MAY-95   3100.0
116            30 24-DEC-97   2900.0
117            30 24-JUL-97   2800.0
118            30 15-NOV-98   2600.0
119            30 10-AUG-99   2500.0

20 rows selected.
```

#### Solution

We can use function stddev() to calculate the standard deviation.

```SQL> select stddev(salary) from EMPLOYEES;

STDDEV(SALARY)
--------------
3909.36575
```
The result can be verified using the following query.
```
SQL> with tbl as (select avg(salary) avg_sal,
count(*) n from employees)
select sqrt(sum((salary-avg_sal)*(salary-avg_sal)/(n-1)))
from EMPLOYEES , tbl;

SQRT(SUM((SALARY-AVG_SAL)*(SALARY-AVG_SAL)/(N-1)))
--------------------------------------------------
3909.36575

```