STDDEV_POP

​

Syntax

​

Aggregate

stddev_pop(value)

​

Window

stddev_pop(value) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])

​

Arguments

​

Aggregate and window

​

Returns

• Type: DOUBLE
• NULL behavior: NULL values are ignored. No values yields NULL; one value yields zero.
• Empty input: Returns NULL on empty input.

​

Properties

​

Usage notes

• Returns DOUBLE. Sample statistics divide by n − 1; population statistics divide by n, where n is the number of included values.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• DISTINCT is not supported for this standard-deviation function.

​

Examples

​

Population standard deviation

WITH t(v) AS (VALUES (0.0), (2.0)) SELECT stddev_pop(v) AS result FROM t;

​

Groups with different numbers of values

WITH sample(grp, v) AS (VALUES (1, 1), (1, 5), (2, 8), (2, CAST(NULL AS INT)))
SELECT grp, round(stddev_pop(CAST(v AS DOUBLE)), 6) AS result
FROM sample GROUP BY grp ORDER BY grp;

​

Limitations

• The result is DOUBLE. Query tools may report the input’s floating-point or decimal type, but that does not change the returned value’s type.
• STDDEV_POP does not accept DISTINCT. To calculate over unique values, remove duplicates in a subquery first.