SQL in a Nutshell, 2nd Edition

Name

VAR_SAMP

Synopsis

Use VAR_SAMP to compute the sample variance of a set of values.

SQL2003 Syntax

VAR_SAMP( numeric_expression )

DB2 and PostgreSQL

Use VARIANCE ( numeric_expression ) to compute sample variance.

MySQL

MySQL provides no function for computing sample variance. There is the VARIANCE function, but in MySQL that function returns the population variance.

Oracle

Oracle supports the standard syntax. You may also use the VARIANCE function, which differs from VAR_SAMP by returning zero (and not NULL) for sets having only a single value.

Oracle also supports analytic syntax:

VAR_SAMP (numeric_expression) OVER (window_clause)

For an explanation of the window_clause, see the section later in this chapter titled Section 4.3.

SQL Server

Use the VAR function.

Example

The following example computes the sample variance for the values 1, 2, and 3:

SELECT * FROM test;
         X
----------
         1
         2
         3
SELECT VAR_SAMP(x) FROM test;
VAR_SAMP(X)
-----------
          1