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(exprexpr) - 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. |
For more information see Statistical Variance and Deviation Functions in the SQL Reference Guide.