## 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