# VARIANCE, VARIANCE_SAMP, VARIANCE_POP, VAR_SAMP, VAR_POP functions

An aggregate function that returns the variance of a set of numbers. This is a mathematical property that signifies how far the values spread apart from the mean. The return value can be zero (if the input is a single value, or a set of identical values), or a positive number otherwise.

**Syntax:**

`{ VARIANCE | VAR[IANCE]_SAMP | VAR[IANCE]_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 `VARIANCE_SAMP()`

and `VARIANCE_POP()`

functions compute the sample
variance and population variance, respectively, of the input values. (`VARIANCE()`

is an alias
for `VARIANCE_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`

.

The functions `VAR_SAMP()`

and `VAR_POP()`

are the same as
`VARIANCE_SAMP()`

and `VARIANCE_POP()`

, respectively. These aliases are
available in Impala 2.0 and later.

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 `VARIANCE()`

and `VARIANCE_SAMP()`

return the
same result, while `VARIANCE_POP()`

uses a slightly different calculation to reflect that the
input data is considered part of a larger “population”.

```
[localhost:21000] > select variance(score) from test_scores;
+-----------------+
| variance(score) |
+-----------------+
| 812.25 |
+-----------------+
[localhost:21000] > select variance_samp(score) from test_scores;
+----------------------+
| variance_samp(score) |
+----------------------+
| 812.25 |
+----------------------+
[localhost:21000] > select variance_pop(score) from test_scores;
+---------------------+
| variance_pop(score) |
+---------------------+
| 811.438 |
+---------------------+
```

This example demonstrates that, because the return value of these aggregate functions is a
`STRING`

, you convert the result with `CAST`

if you need to do further
calculations as a numeric value.

```
[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 STDDEV, STDDEV_SAMP, STDDEV_POP functions
for details about the standard deviation property.