VAR_SAMP

​

Syntax

​

Aggregate

var_samp(value)

​

Window

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

​

Arguments

​

Aggregate and window

​

Returns

• Type: DOUBLE
• NULL behavior: NULL values are ignored. Fewer than two non-NULL values yields NULL.
• 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 removes duplicates in grouped aggregation. Do not use it to remove duplicates in a window call.

​

Examples

​

Sample variance

WITH t(v) AS (VALUES (1.0), (3.0), (5.0)) SELECT var_samp(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(var_samp(CAST(v AS DOUBLE)), 6) AS result
FROM sample GROUP BY grp ORDER BY grp;

​

Limitations

• DISTINCT removes duplicates in grouped calls.
• 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.
• DISTINCT in a window call currently has no effect: duplicate rows still contribute to the result. Use grouped DISTINCT aggregation when duplicate removal is required.