Categories:
Aggregate Functions (General) , Window Functions

STDDEV_SAMP

Returns the sample standard deviation (square root of sample variance) of non-NULL values. If all records inside a group are NULL, returns NULL.

Aliases:
STDDEV

Syntax

STDDEV_SAMP([DISTINCT] <expr>)

Arguments

expr
An expression that evaluates to a numeric value (integer, floating point, or fixed point).

Usage Notes

  • For single-record inputs, STDDEV_SAMP and STDDEV both return NULL. This is different from the Oracle behavior, where STDDEV_SAMP returns NULL for a single record and STDDEV returns 0.

Examples

create or replace table aggr(k int, v decimal(10,2), v2 decimal(10, 2));
insert into aggr values(1, 10, null);
insert into aggr values(2, 10, 11), (2, 20, 22), (2, 25,null), (2, 30, 35);
select k, stddev_samp(v), stddev_samp(v2) from aggr group by k;

---+----------------+-----------------+
 k | stddev_samp(v) | stddev_samp(v2) |
---+----------------+-----------------+
 1 | [NULL]         | [NULL]          |
 2 | 8.539125634    | 12.013880859    |
---+----------------+-----------------+