Statistical Variance and Deviation Functions

<< Click to Display Table of Contents >>

Navigation:  Glossary >

Statistical Variance and Deviation Functions

Previous pageReturn to chapter overviewNext page

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.