VAR_SAMP

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 or an analytic function. Applied to an empty set, it returns null. Given an input set of one element, VAR_SAMP returns null.