How to Calculate Standard Deviation in BigQuery
Last updated August 23, 2026 · By the SaturnSQL team
STDDEV is an alias for STDDEV_SAMP, the sample standard deviation. Use STDDEV_POP when your rows are the whole population rather than a sample.
SELECT AVG(duration_ms) AS mean_ms,
STDDEV(duration_ms) AS sd_ms
FROM requests;Sample or population
STDDEV_SAMP divides by n-1 and STDDEV_POP divides by n. On large tables the two are nearly identical, but STDDEV_SAMP returns NULL when there is only one row, because n-1 is zero, while STDDEV_POP returns 0. VAR_SAMP and VAR_POP are the matching variance functions. All of them ignore NULL inputs.
SELECT STDDEV_SAMP(duration_ms) AS sd_sample,
STDDEV_POP(duration_ms) AS sd_population
FROM requests;Per group and as a window
Like other aggregates, these work with GROUP BY, and with an OVER clause when you want the deviation alongside each row rather than collapsed.
SELECT endpoint,
AVG(duration_ms) AS mean_ms,
STDDEV(duration_ms) AS sd_ms,
COUNT(*) AS n
FROM requests
GROUP BY endpoint
HAVING n > 30
ORDER BY sd_ms DESC;Finding outliers
A window function gives you the mean and deviation of the whole set on every row, which is enough to flag rows more than a few deviations out.
SELECT *
FROM (
SELECT endpoint, duration_ms,
AVG(duration_ms) OVER () AS mean_ms,
STDDEV(duration_ms) OVER () AS sd_ms
FROM requests
)
WHERE sd_ms > 0
AND ABS(duration_ms - mean_ms) > 3 * sd_ms;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free