MySQL - Standard Deviation

MySQL: Calculating Standard Deviation

Standard deviation is a statistical measure that quantifies the amount of variation or dispersion of a set of data values. A low standard deviation indicates that the values tend to be close to the mean (or expected value) of the set, while a high standard deviation indicates that the values are spread out over a wider range.

MySQL provides several aggregate functions to calculate standard deviation directly within your queries.

Population vs. Sample Standard Deviation

Before using the functions, it’s crucial to understand the difference between a population and a sample:

• Population: This includes every member of a group you are studying. For example, if you have a table of all employees in your company, that is the population.
• Sample: This is a smaller, manageable subset of a larger population. For example, if you survey 100 random employees to estimate the satisfaction of the entire company, that group of 100 is a sample.

The formulas for calculating them are slightly different, and MySQL has separate functions for each.

MySQL Standard Deviation Functions

• For Population Standard Deviation: - STDDEV_POP(expression): This is the standard, explicit function and is recommended for clarity. - STD(expression): A synonym for STDDEV_POP(). - STDDEV(expression): Another synonym for STDDEV_POP(), provided for compatibility with other database systems.
• For Sample Standard Deviation: - STDDEV_SAMP(expression): This is the function to calculate the standard deviation of a sample.

If a query returns no rows or the expression evaluates to NULL for all rows, these functions return NULL.

Practical Examples

Let’s use our employees table, assuming the data represents the entire population of a small company.

-- If you need to create the table:
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

INSERT INTO employees (department, salary) VALUES
('Engineering', 95000.00), ('Engineering', 110000.00),
('Sales', 72000.00), ('Sales', 68000.00),
('HR', 65000.00), ('Engineering', 125000.00),
('Sales', 88000.00);

1. Calculating Population Standard Deviation for All Salaries

To find the overall dispersion of salaries across the company, we use STDDEV_POP().

SELECT STDDEV_POP(salary) AS salary_std_dev FROM employees;

salary_std_dev
20387.56417387

2. Calculating Sample Standard Deviation

If we were treating our data as a sample of a much larger organization, we would use STDDEV_SAMP(). Notice the result is slightly larger.

SELECT STDDEV_SAMP(salary) AS salary_sample_std_dev FROM employees;

salary_sample_std_dev
21984.87103738

3. Using Standard Deviation with GROUP BY

A more powerful use is to compare the salary variation within each department.

SELECT
    department,
    AVG(salary) AS average_salary,
    STDDEV_POP(salary) AS salary_std_dev
FROM
    employees
GROUP BY
    department;

This query reveals that the Engineering department has a higher average salary but also much greater salary variation than the Sales department.

department	average_salary	salary_std_dev
Engineering	110000.00	12472.19128925
Sales	76000.00	8944.27191
HR	65000.00	0.0

Modern Approach: Using Window Functions

Since MySQL 8.0, you can use standard deviation functions as window functions. This allows you to show the deviation next to each row’s data without collapsing the rows using GROUP BY.

For example, let’s show each employee’s salary alongside the standard deviation for their entire department.

SELECT
    id,
    department,
    salary,
    STDDEV_POP(salary) OVER (PARTITION BY department) AS department_salary_std_dev
FROM
    employees;

The OVER (PARTITION BY department) clause tells MySQL to calculate the standard deviation for each group of rows that have the same department value.

id	department	salary	department_salary_std_dev
1	Engineering	95000.00	12472.19128925
2	Engineering	110000.00	12472.19128925
6	Engineering	125000.00	12472.19128925
5	HR	65000.00	0.0
3	Sales	72000.00	8944.27191
4	Sales	68000.00	8944.27191
7	Sales	88000.00	8944.27191

This approach is incredibly powerful for creating detailed analytical reports directly from your database.