SQL Standard Deviation (STDDEV_POP / STDDEV_SAMP)

The last lesson's variance is in squared units, a variance of 400 on a salary column means nothing intuitive on its own. Standard deviation is its square root, back in the original unit (dollars, in this case), which is why it's usually the more directly useful number to report.

Learning Objectives

Define standard deviation as variance's square root, and compute both population and sample forms.
Know the real function names per database, including SQL Server's own, differently-spelled convention.
Recognize the genuine MySQL/MariaDB vs PostgreSQL/Oracle gotcha in what an unqualified STDDEV() actually returns.

Variance

Squared units

Square Root

Back to the original unit

Standard Deviation

Population or sample form

The Real Function Names, by Database

Database	Population Std. Deviation	Sample Std. Deviation
MySQL / MariaDB / PostgreSQL / Oracle	STDDEV_POP(col)	STDDEV_SAMP(col)
SQL Server	STDEVP(col)	STDEV(col) — one D, not two, and no underscore
SQLite	No built-in function	No built-in function

The Unqualified STDDEV() Gotcha

Database	What Plain STDDEV() Actually Means
MySQL / MariaDB	Population standard deviation, STDDEV() and STD() are both synonyms for STDDEV_POP(), not STDDEV_SAMP().
PostgreSQL / Oracle	Sample standard deviation, STDDEV() is a synonym for STDDEV_SAMP() there instead.

NOTE

The exact same unqualified function name, STDDEV(), returns a genuinely different calculation depending on which of these databases runs it. Writing STDDEV_POP() or STDDEV_SAMP() explicitly avoids ever depending on which convention a given database happens to follow.

SQLite: Deriving It From Variance

The same manual variance formula from the last lesson, wrapped in SQRT(), since SQLite's core engine has no STDDEV_POP, STDDEV_SAMP, or STDDEV function built in either.

SQLite (population standard deviation, computed manually)

SELECT SQRT(AVG(salary * salary) - AVG(salary) * AVG(salary)) AS population_stddev
FROM employees
WHERE department_id = 3;

Try It Yourself

Compute the population standard deviation of salaries in department id 3, on MySQL, using the explicit, unambiguous function name.

Answer

SELECT STDDEV_POP(salary) AS population_stddev
FROM employees
WHERE department_id = 3;

Frequently Asked Questions

Why would a query ever need standard deviation instead of just variance?

Standard deviation is in the original unit, directly comparable to the values themselves (dollars, days, whatever the column measures), where variance is in squared units and much harder to interpret intuitively on its own.

Does SQL Server's STDEV/STDEVP have the same population-vs-sample meaning as STDDEV_SAMP/STDDEV_POP elsewhere?

Yes, the underlying statistics are identical, STDEV is sample (dividing by N - 1), STDEVP is population (dividing by N), only the spelling and naming convention differs from the other databases' STDDEV_SAMP/STDDEV_POP.

Common Beginner Mistakes

Assuming unqualified STDDEV() means the same thing on every database

It genuinely doesn't, MySQL and MariaDB's STDDEV() is population standard deviation, while PostgreSQL's and Oracle's STDDEV() is sample standard deviation, the exact opposite default. Writing STDDEV_POP() or STDDEV_SAMP() explicitly sidesteps the whole ambiguity.

Writing STDDEV() or STDEV() on SQL Server expecting MySQL's spelling

SQL Server's functions are STDEV (sample) and STDEVP (population), one D, no underscore, genuinely different spelling and naming from STDDEV_SAMP/STDDEV_POP elsewhere, not interchangeable text.

Best Practices

Always write the explicit _POP or _SAMP suffix (or SQL Server's distinct P-suffix convention), never rely on an unqualified STDDEV()'s default, it isn't consistent across databases.
Report standard deviation alongside the average, not variance, it's in the same, directly interpretable unit as the data itself.
On SQLite, derive it from the same manual variance formula wrapped in SQRT(), rather than assuming a built-in function exists.

Interview Questions

What does the plain, unqualified STDDEV() function return on MySQL versus on PostgreSQL?

On MySQL (and MariaDB), STDDEV() is a synonym for STDDEV_POP(), the population standard deviation. On PostgreSQL (and Oracle), STDDEV() is a synonym for STDDEV_SAMP() instead, the sample standard deviation. Same unqualified name, genuinely different calculation, which is why writing the explicit suffix is the safer habit.

How is standard deviation related to variance, and why report one over the other?

Standard deviation is the square root of variance, which brings it back into the data's original unit instead of a squared one, making it the more directly interpretable of the two for reporting spread to a human reader, even though variance is what the underlying statistical formulas are usually built from.

Summary

Standard deviation is variance's square root, in the original unit. STDDEV_POP/STDDEV_SAMP is explicit and safe everywhere it exists, SQL Server's STDEV/STDEVP covers the same two forms under different spelling, and the unqualified STDDEV() name is a genuine, well-documented trap, population on MySQL/MariaDB, sample on PostgreSQL/Oracle. SQLite has neither, built from the same manual variance formula.

What's Next?

MEDIAN, the middle value of a sorted group, with Oracle's dedicated native function and the real, differently-shaped alternative every other database here needs instead.

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.

Or try 50 SQL practice questions →