Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

VAR_SAMP and VAR_POP Functions

The VAR_SAMP and VAR_POP functions return the sample and population variance of a set of numeric values (integer, decimal, or floating-point). The result of the VAR_SAMP function is equivalent to the squared sample standard deviation of the same set of values.

VAR_SAMP and VARIANCE are synonyms for the same function.

Syntax

Copy
VAR_SAMP | VARIANCE ( [ DISTINCT | ALL ] expression) VAR_POP ( [ DISTINCT | ALL ] expression)

The expression must have an integer, decimal, or floating-point data type. Regardless of the data type of the expression, the return type of this function is a double precision number.

Note

The results of these functions might vary across data warehouse clusters, depending on the configuration of the cluster in each case.

Usage Notes

When the sample variance (VARIANCE or VAR_SAMP) is calculated for an expression that consists of a single value, the result of the function is NULL not 0.

Examples

The following query returns the rounded sample and population variance of the NUMTICKETS column in the LISTING table.

Copy
select avg(numtickets), round(var_samp(numtickets)) varsamp, round(var_pop(numtickets)) varpop from listing; avg | varsamp | varpop -----+---------+-------- 10 | 54 | 54 (1 row)

The following query runs the same calculations but casts the results to decimal values.

Copy
select avg(numtickets), cast(var_samp(numtickets) as dec(10,4)) varsamp, cast(var_pop(numtickets) as dec(10,4)) varpop from listing; avg | varsamp | varpop -----+---------+--------- 10 | 53.6291 | 53.6288 (1 row)