Skip to main content

Categories: Math, Aggregate, Window

STDDEV_SAMP

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

Syntax​

STDDEV_SAMP(col_name NUMERIC) → DOUBLE​

  • col_name: The name of the column for which to return the sample standard deviation. The values in the column must be numbers, such as INT, DOUBLE, or FLOAT.

Examples

STDDEV_SAMP example 
SELECT STDDEV_SAMP(tip_amount) FROM Samples."samples.dremio.com"."NYC-taxi-trips"
-- 2.259665033866297
STDDEV_SAMP example: Window function with sliding window frame 
SELECT city, state, pop, STDDEV_SAMP(pop)
  OVER (PARTITION BY state ORDER BY city ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
FROM Samples."samples.dremio.com"."zips.json";
-- city, state, pop, EXPR$3
-- 98791, AK, 5345, 6476.127649555198
-- AKHIOK, AK, 13309, 6102.649479256257
-- AKIACHAK, AK, 481, 6429.897977417682
-- ...

Usage Notes​

The STDDEV_SAMP function supports optional PARTITION BY, ORDER_BY, and sliding window frame subclauses. See Window Functions for more information and syntax.