Standard Deviation in SQL

An average says where the values sit. It says nothing about whether they cluster tightly or scatter. Garden Shop's Tools and Pots & Planters both average $13.17, but the tools run from $8 to $22 and the pots from $6.50 to $19. The standard deviation puts a number on the scatter, in the same units as the values.

select round(avg(price), 2) as avg_price,
       round(stddev_samp(price), 2) as price_stddev,
       round(var_samp(price), 2) as price_variance
from products

Reading the number

Garden Shop's prices average $14.36 with a standard deviation of $7.51, so a typical product sits about seven or eight dollars from the average. The variance is the standard deviation squared (56.34 here). It is in squared dollars, which is why reports quote the standard deviation instead.

Sample or population

There are two versions of each function. stddev_samp treats the rows as a sample of something larger and divides by one less than the row count. stddev_pop treats them as the whole population and divides by the row count:

select round(stddev_samp(price), 2) as sample_stddev,
       round(stddev_pop(price), 2) as population_stddev
from products

With 24 products the difference is small (7.51 against 7.35). Plain stddev() means the sample version in DuckDB and PostgreSQL. In MySQL it means the population version, so name the one you mean.

A group of one

A single value has no spread to estimate from, so the sample version returns NULL while the population version returns 0:

select stddev_samp(price) as sample_stddev,
       stddev_pop(price) as population_stddev
from products
where product_id = 1

Expect those NULLs whenever you group finely enough that some groups hold one row.