VARIANCE Function [Aggregate]

Returns the variance of a set of numbers.

Syntax

VARIANCE ( [ ALL ] expression )

Parameters

Parameter

Description

expression

Any numeric data type (FLOAT, REAL, or DOUBLE) expression.

The expression (commonly a column name) whose sample-based variance is calculated over a set of rows.

Returns

DOUBLE

Examples

Given this data:
SELECT Salary FROM Employees WHERE DepartmentID = 300

      salary

 51432.000

 57090.000

 42300.000

   43700.00

  36500.000

138948.000

  31200.000

    58930.00

    75400.00

The following statement returns the value 1063923790.99999994:

SELECT VARIANCE ( Salary ) FROM Employees
WHERE DepartmentID = 300
Given this data:
SELECT UnitPrice FROM Products WHERE name = 'Tee Shirt'

UnitPrice

            9.00

          14.00

          14.00

The following statement returns the value 8.33333333333334327:

SELECT VARIANCE ( UnitPrice ) FROM Products
WHERE name = 'Tee Shirt'

Usage

The formula used to calculate VARIANCE is


The formula used by the VARIANCE function to calculate variance is var equals n times the sum of x squared minus the sum of x squared divided by n times n minus one

VARIANCE returns a result of data type double-precision floating-point. If applied to the empty set, the result is NULL, which returns NULL for a one-element input set.

VARIANCE does not support the keyword DISTINCT. A syntax error is returned if DISTINCT is used with VARIANCE.

Standards and Compatibility

  • SQL—Vendor extension to ISO/ANSI SQL grammar.

  • Sybase—Not supported by Adaptive Server Enterprise.

Related concepts
Windowing Aggregate Function Usage
Related reference
STDDEV Function [Aggregate]
STDDEV_SAMP Function [Aggregate]