Statistical Variance and Deviation Functions

Each of these functions takes a set of numbers, ignores nulls, and can be used as either an aggregate function or an analytic function.

The relationships among these functions are described in the following table:

Function purpose Function name Formula Comments
Population variance VAR_POP(expr) ( SUM(expr*expr) - SUM(expr)*SUM(expr) / COUNT(expr)) / COUNT(expr) Applied to an empty set, it returns null.
Population standard deviation STDDEV_POP(expr) Square root of the population variance (VAR_POP). When VAR_POP returns null, STDDEV_POP returns null.
Sample variance VAR_SAMP(expr) (SUM(expr*expr) - SUM(expr)*SUM(expr) / COUNT(expr)) / (COUNT(expr)−1) Applied to an empty set, it returns null. Applied to an input set of one element, VAR_SAMP returns null.
Sample standard deviation STDDEV_SAMP(expr) Square root of the sample variance (VAR_SAMP). Applied to only 1 row of input data, STDDEV_SAMP returns null.

VAR_POP

Returns the population variance of a non-null set of numbers (nulls being ignored).

VAR_POP uses the following calculation:

(SUM(expr*expr) - SUM(expr)*SUM(expr) / COUNT(expr)) / COUNT(expr)

In other words, for a given set of non-null values, using S1 as the sum of the values and S2 as the sum of the squares of the values, VAR_POP returns the result (S2-S1*S1/N)/N.

You can use VAR_POP as either an aggregate and analytic function. Applied to an empty set, it returns null.

Syntax

VAR_POP ( [DISTINCT | ALL] <number-expression> )

where ALL includes (and DISTINCT excludes) duplicate values in the input set. ALL is the default. When the the input set has no non-null data, VAR_POP returns NULL.

<number-expression> can be any any numeric expression.

STDDEV_POP

Population Standard Deviation. Returns the returns the square root of the population variance (VAR_POP) for , evaluated for each row remaining in the group.

Syntax

STDDEV_POP ( [DISTINCT | ALL] <number-expression> )

where ALL includes (and DISTINCT excludes) duplicate values in the input set. ALL is the default. When the the input set has no non-null data, STDDEV_POP returns NULL.

<<number-expression>> can be any any numeric expression.

VAR_SAMP

Returns the sample variance of a non-null set of numbers (nulls being ignored).

Syntax

VAR_SAMP ( [DISTINCT | ALL] <number-expression> )

where ALL includes (and DISTINCT excludes) duplicate values in the input set. ALL is the default. When the the input set has no non-null data, VAR_SAMP returns NULL.

<number-expression> can be any any numeric expression.

VAR_SAMP uses the following calculation:

(SUM(expr*expr) - SUM(expr)\*SUM(expr) / COUNT(expr)) / (COUNT(expr)-1)

In other words, for a given set of non-null values, using S1 as the sum of the values and S2 as the sum of the squares of the values, VAR_POP returns the result (S2-S1*S1/N)/(N-1).

You can use VAR_SAMP as either an aggregate and analytic function. Applied to an empty set, it returns null. Given an input set of one element, VAR_SAMP returns null.

STDDEV_SAMP

Returns the statistical standard deviation of all values in <number-expression>, evaluated for each row remaining in the group and defined as the square root of the [sample variance (VAR_SAMP)]#var_samp).

Syntax

STDDEV_SAMP ( [DISTINCT | ALL] <<number-expression>> )

where ALL includes (and DISTINCT excludes) duplicate values in the input set. ALL is the default. When the the input set has no non-null data, STDDEV_SAMP returns NULL.

<number-expression> can be any any numeric expression.

STD_DEV is an alias of STDDEV_SAMP.