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.
-- PostgreSQL SELECT STDDEV_SAMP(salary), VAR_POP(salary), PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median FROM employees;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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 |
-- 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.
STDDEVandVARIANCEmean "sample" in some databases and "population" in others. Use the explicit_SAMPor_POPname. - Sample versus population. Dividing by
n - 1corrects 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) / 2and(n + 2) / 2pair selects; for an odd count both expressions point at the same row. - Performance. Medians need the data sorted, so they cost more than
AVG.