Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Aggregate Functions

SQL STDDEV, VARIANCE and MEDIAN

Reference for statistical aggregates in SQL: standard deviation, variance and median across dialects, and portable SQLite formulas that work everywhere.

Statistical aggregates describe how values are spread: variance and standard deviation measure the spread around the mean, and the median is the middle value. Most databases offer them as built-in functions, but with different names, and the SQL standard only defines some.

SQLite has none of them in its core, so on this page the runnable examples compute the same numbers from AVG, SUM and COUNT, and with window functions. That also shows you what the built-ins do.

SQL
-- PostgreSQL
SELECT STDDEV_SAMP(salary), VAR_POP(salary),
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median
FROM employees;

Try it

SQL Variance and standard deviation from first principles ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Median with window functions ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL Mean versus median per department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
STDDEV_SAMP(expr)    STDDEV_POP(expr)
VAR_SAMP(expr)       VAR_POP(expr)
PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY expr)
PERCENTILE_DISC(p) WITHIN GROUP (ORDER BY expr)
Name Description
VAR_POP, STDDEV_POP Population variance and standard deviation: divide by n. Use when the rows are the whole population.
VAR_SAMP, STDDEV_SAMP Sample variance and standard deviation: divide by n - 1. Use when the rows are a sample.
PERCENTILE_CONT(p) Interpolated percentile; 0.5 is the median and may fall between two values.
PERCENTILE_DISC(p) The first actual value whose cumulative position reaches p.

Returns: a number, or NULL if there are no values (sample variants also return NULL for exactly one value).

Dialect differences

Database Variance / deviation Median
SQLite Not built in (sql.js adds stdev and median as extensions; stock SQLite does not) Compute with window functions
PostgreSQL VAR_SAMP, STDDEV_SAMP and the _POP forms; VARIANCE and STDDEV mean the sample versions PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x)
MySQL VAR_POP, STDDEV_POP, VAR_SAMP, STDDEV_SAMP; VARIANCE and STDDEV mean the population versions No built-in; use window functions
SQL Server VAR / STDEV (sample) and VARP / STDEVP (population) PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) OVER () (a window function only)
Oracle VAR_POP, VAR_SAMP, STDDEV_POP, STDDEV_SAMP; VARIANCE and STDDEV mean the sample versions MEDIAN(x) built in
SQL
-- Oracle
SELECT MEDIAN(salary) FROM employees;

-- SQL Server
SELECT DISTINCT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) OVER () AS median
FROM employees;

Usage notes

  • Unqualified names are a trap. STDDEV and VARIANCE mean "sample" in some databases and "population" in others. Use the explicit _SAMP or _POP name.
  • Sample versus population. Dividing by n - 1 corrects for estimating the mean from the same data. With one row the sample variance is undefined (NULL).
  • NULLs are ignored by all of these, as for AVG.
  • Median is robust. One huge salary moves the mean a lot and the median hardly at all, so report both for skewed data.
  • Even counts. For an even number of rows the median is the mean of the two middle values, which is what the (n + 1) / 2 and (n + 2) / 2 pair selects; for an odd count both expressions point at the same row.
  • Performance. Medians need the data sorted, so they cost more than AVG.