SQL VAR Function

VAR returns the statistical variance of the given values.

VAR ignores NULL values.

Use VAR if the values represent a sample.

Use VARP if the values represent the entire population.

Example

#

This example returns the variance of all sales.

SELECT VAR(TotalAmount) AS 'Variance'
  FROM [Order]
Result:  1 record
Variance
3962543.95098209

Syntax

Syntax of the VAR function.

VAR(value)

value -- a number or numeric column.

More Examples

VAR with COLUMN

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
Problem: List suppliers with the statistical variance of their product prices.
SELECT S.CompanyName, 
       VAR(UnitPrice) AS 'Price Variance'
  FROM Product P
  JOIN Supplier S ON S.Id = P.SupplierId
 GROUP BY S.CompanyName
Result:  29 records
CompanyName Price Standard Deviation
Aux joyeux ecclésiastiques 30135.125
Bigfoot Breweries 5.33333333333331
Cooperativa de Quesos 'Las Cabras' 144.5
Escargots Nouveaux NULL
Exotic Liquids 24.3333333333333

Note: A NULL value is returned when a supplier has less than 2 products.


You may also like



Guides


vsn 3.1