SQL Server STDEV() Function

Summary: in this tutorial, you will learn how to use the SQL Server STDEV() function to calculate the sample standard deviation of a set of values.

Introduction to the SQL Server STDEV() function

Standard deviation measures the variation or dispersion of values in a set of values.

A low standard deviation indicates that values tend to be close to the mean whereas a high standard deviation shows the values are spread out over a wider range.

There are two types of standard deviations:

• Population standard deviation analyzes the entire population.
• Sample standard deviation analyzes a subset (sample) of the population.

In SQL Server, you can use the STDEV() function to calculate the sample standard deviation of a set of values.

Here’s the syntax of the STDEV function:

STDEV ( [ ALL | DISTINCT ] expression )  Code language: SQL (Structured Query Language) (sql)

In this syntax:

• The ALL option instructs the function to apply to all values including duplicates. The default value is ALL.
• The DISTINCT option instructs the function to apply to unique values instead.
• The expression is a table column or an expression that contains the values to which the function applies.

The STDEV() function returns the standard deviation of values in the table column specified by the expression as a float number.

SQL Server STDEV() function example

First, create a new table called salaries that stores the salaries of employees:

CREATE TABLE salaries (
    id INT IDENTITY PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    salary DEC(10, 2) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the salaries table:

INSERT INTO
  salaries (name, salary)
VALUES
  ('John', 60000.00),
  ('Alice', 65000.00),
  ('Bob', 70000.00),
  ('Emily', 55000.00),
  ('Michael', 75000.00),
  ('Sophia', 80000.00),
  ('David', 50000.00),
  ('Emma', 75000.00),
  ('James', 110000.00),
  ('Olivia', 120000.00);Code language: SQL (Structured Query Language) (sql)

Third, retrieve data from the salaries table:

SELECT * FROM salaries;Code language: SQL (Structured Query Language) (sql)

Output:

id | name    | salary
---+---------+---------
1  | John    | 60000.00
2  | Alice   | 65000.00
3  | Bob     | 70000.00
4  | Emily   | 55000.00
5  | Michael | 75000.00
6  | Sophia  | 80000.00
7  | David   | 50000.00
8  | Emma    | 75000.00
9  | James   | 110000.00
10 | Olivia  | 120000.00
(10 rows)Code language: plaintext (plaintext)

Fourth, use the STDEV() function to calculate the sample standard deviation of the salaries:

SELECT STDEV(salary) salary_stdev
FROM salaries;Code language: SQL (Structured Query Language) (sql)

Output:

salary_stdev
-----------------
22705.84848790187Code language: plaintext (plaintext)

To make the standard deviation more readable, you can round it using the ROUND() function:

SELECT
  ROUND(STDEV (salary), 0) salary_stdev
FROM
  salaries;Code language: SQL (Structured Query Language) (sql)

Output:

salary_stdev
------------
22706.00Code language: plaintext (plaintext)

Fifth, calculate the differences between the standard deviation and the mean:

SELECT
  ROUND(AVG(salary) - STDEV (salary), 0) low,
  ROUND(AVG(salary) + STDEV (salary), 0) high
FROM
  salaries;Code language: SQL (Structured Query Language) (sql)

Output:

low      | high
---------+---------
53294.00 | 98706.00
(1 row)Code language: plaintext (plaintext)

The low is 53,294 and the high is 98,706.

Based on the standard deviation, we can show which salary is within one standard deviation of the mean. In other words, we have a “standard” way of knowing what is low, normal, and high salary:

The chart shows that:

• John, Alice, Bob, Emily, Michael, Sophia, and Emma have the normal salary.
• David has a low salary.
• James and Olivia have a high salary.

Summary

• Use the STDEV() function to calculate the sample standard deviation of a set of values.