# STDDEV, STDDEV_SAMP, STDDEV_POP functions

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

**Syntax:**

`{ STDDEV | STDDEV_SAMP | STDDEV_POP } ([DISTINCT | ALL] ``expression`)

This function works with any numeric data type.

**Return type:** `DOUBLE`

in Impala 2.0 and higher; `STRING`

in earlier
releases

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 population

.

```
[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) | |
+--------------------+--------------+---------+
```

**Restrictions:**

This function cannot be used in an analytic context. That is, the `OVER()`

clause is not allowed at all with this function.

**Related information:**

The `STDDEV()`

, `STDDEV_POP()`

, and `STDDEV_SAMP()`

functions compute the standard deviation (square root of the variance) based on the results
of `VARIANCE()`

, `VARIANCE_POP()`

, and
`VARIANCE_SAMP()`

respectively. See VARIANCE, VARIANCE_SAMP, VARIANCE_POP, VAR_SAMP, VAR_POP functions for details about the variance property.