# STDDEV, STDDEV_SAMP, STDDEV_POP Functions

An aggregate function that standard deviation of a set of numbers.

This function works with any numeric data type.

Currently, the return value is always a `STRING`.
The return type is subject to change in future releases.
Always use `CAST()` to convert the result
to whichever data type is appropriate for your computations.

This function is typically used in mathematical formulas related to probability distributions.

The
`STDDEV_POP()`
and
`STDDEV_SAMP()`
functions
compute the
population standard deviation
and
sample standard deviation, respectively,
of the input values.
(`STDDEV()` is an alias for `STDDEV_SAMP()`.)
Both functions evaluate all input rows matched by the query.
The difference is that `STDDEV_SAMP()`
is scaled by `1/(N-1)`
while `STDDEV_POP()`
is scaled by `1/N`.

If no input rows match the query, the result of any of these functions is `NULL`.
If a single input row matches the query, the result of any of these functions is `"0.0"`.

**Examples:**

This example demonstrates how `STDDEV()` and
`STDDEV_SAMP()` return the same result,
while `STDDEV_POP()` uses a slightly
different calculation to reflect that the input
data is considered part of a larger

[localhost:21000] > select stddev(score) from test_scores; +---------------+ | stddev(score) | +---------------+ | 28.5 | +---------------+ [localhost:21000] > select stddev_samp(score) from test_scores; +--------------------+ | stddev_samp(score) | +--------------------+ | 28.5 | +--------------------+ [localhost:21000] > select stddev_pop(score) from test_scores; +-------------------+ | stddev_pop(score) | +-------------------+ | 28.4858 | +-------------------+

This example demonstrates that, because the return value of these
aggregate functions is a `STRING`,
you must currently convert the result with `CAST`.

[localhost:21000] > create table score_stats as select cast(stddev(score) as decimal(7,4)) `standard_deviation`, cast(variance(score) as decimal(7,4)) `variance` from test_scores; +-------------------+ | summary | +-------------------+ | Inserted 1 row(s) | +-------------------+ [localhost:21000] > desc score_stats; +--------------------+--------------+---------+ | name | type | comment | +--------------------+--------------+---------+ | standard_deviation | decimal(7,4) | | | variance | decimal(7,4) | | +--------------------+--------------+---------+

<< | ||

Terms and Conditions Privacy Policy |