The VARIANCE function

The VARIANCE function returns the variance for the column of the selected rows without Bessel correction. This is the population variance. The function has an according alias VARIANCE_POP. 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 Informix® Guide to SQL: Syntax.