Compute Standard Deviation and Variance

Measure how spread out values are with `STDDEV_SAMP` / `VAR_SAMP` in MySQL, MariaDB, and PostgreSQL, or `STDEV` / `VAR` in SQL Server.

Created May 5, 2026 Last updated May 5, 2026 0/5 supported engines validation-green 1 example 3 scenarios

Docker-validated Not currently validation-green

Example 1

Measure the spread of temperature readings from a sensor

The three readings 10, 20, and 30 have a mean of 20. Sample variance divides the total squared deviation from the mean by n − 1 = 2: ((10−20)² + (20−20)² + (30−20)²) / 2 = (100 + 0 + 100) / 2 = 100. Sample standard deviation is √100 = 10. The population variants (STDDEV_POP, VAR_POP, STDEVP, VARP) divide by n = 3 instead, giving variance ≈ 66.67 and standard deviation ≈ 8.16. Use sample variants when working with a subset of a larger population; use population variants when the data represents the complete set.

MySQL MariaDB PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE readings (sensor_id INT, temp_c INT);

INSERT INTO
  readings
VALUES
  (1, 10),
  (1, 20),
  (1, 30);

Validated query

SQL

SELECT
  STDDEV_SAMP(temp_c) AS stddev_samp,
  VAR_SAMP(temp_c) AS var_samp
FROM
  readings;

Expected result

stddev_samp	var_samp
10	100

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE readings (sensor_id INT, temp_c INT);

INSERT INTO
  readings
VALUES
  (1, 10),
  (1, 20),
  (1, 30);

Validated query

SQL

SELECT
  STDEV (temp_c) AS stddev_samp,
  VAR (temp_c) AS var_samp
FROM
  readings;

Expected result

stddev_samp	var_samp
10	100

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE readings (sensor_id INT, temp_c INT);

INSERT INTO
  readings
VALUES
  (1, 10),
  (1, 20),
  (1, 30);

Validated query

SQL

SELECT
  SQRT(
    (
      SUM(temp_c * temp_c) - SUM(temp_c) * SUM(temp_c) * 1.0 / COUNT(*)
    ) / (COUNT(*) - 1)
  ) AS stddev_samp,
  (
    SUM(temp_c * temp_c) - SUM(temp_c) * SUM(temp_c) * 1.0 / COUNT(*)
  ) / (COUNT(*) - 1) AS var_samp
FROM
  readings;

Expected result

stddev_samp	var_samp
10	100

MySQL, MariaDB, and PostgreSQL use STDDEV_SAMP/VAR_SAMP. SQL Server uses STDEV/VAR. SQLite requires an arithmetic workaround using SUM and SQRT.

Useful when

Where this command helps.

checking whether sensor readings are consistent or highly variable before computing a mean
comparing the spread of exam scores across different test sittings to detect grading inconsistency
computing quality-control thresholds as mean ±2 standard deviations from a manufacturing process

Explanation

What the command is doing.

Standard deviation and variance quantify how far values are spread around their mean. The sample variants (STDDEV_SAMP, VAR_SAMP) divide the sum of squared deviations by n − 1 (Bessel's correction), making them unbiased estimators when working with a sample drawn from a larger population. The population variants (STDDEV_POP, VAR_POP) divide by n and apply when the dataset is the entire population. MySQL, MariaDB, and PostgreSQL expose both STDDEV_SAMP / STDDEV_POP and VAR_SAMP / VAR_POP. SQL Server uses STDEV / STDEVP and VAR / VARP — different names, identical behaviour. SQLite lacks built-in functions for either, but sample variance can be computed as (SUM(xx) − SUM(x)SUM(x)1.0/COUNT()) / (COUNT(*) − 1) and sample standard deviation as SQRT of that result. All four variants also function as window functions with OVER(), enabling per-row z-score calculations alongside the original data.

Compute Standard Deviation and Variance

Measure the spread of temperature readings from a sensor

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.