The VARIANCE function

The VARIANCE function returns the variance for a sample of values as an unbiased estimate of the variance for all rows selected. It computes the following value:

(SUM(Xi**2) - (SUM(Xi)**2)/N)/(N-1)

In this example, Xi is each value in the column and N is the total number of values in the column. You can apply the VARIANCE function only to numeric columns. The following query finds the variance on a population:

SELECT VARIANCE(age) FROM u_pop WHERE age > 21;

As with the other aggregates, the VARIANCE function applies to the rows of a group when the query includes a GROUP BY clause, which the following example shows:

SELECT VARIANCE(age) FROM u_pop
   GROUP BY birth
   WHERE VARIANCE(age) > 21;

Nulls are ignored unless every value in the specified column is null. If every column value is null, the VARIANCE function returns a null for that column. For more information about the VARIANCE function, see the Expression segment in the HCL OneDB™ Guide to SQL: Syntax.