SQL Server/T-SQL Tutorial/Analytical Functions/VAR
The VAR() return the measure of how far the extreme low range or high range value is from the middle
or mean value of the range, weighted by the greatest concentration of similar values.
11>
12> Create Table MyValues (MyValue Float)
13> GO
1>
2> Insert Into MyValues (MyValue) SELECT 2
3> Insert Into MyValues (MyValue) SELECT 3
4> Insert Into MyValues (MyValue) SELECT 4
5> Insert Into MyValues (MyValue) SELECT 5
6> Insert Into MyValues (MyValue) SELECT 6
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT VAR(MyValue) FROM MyValues
3> GO
------------------------
2.5
(1 rows affected)
1>
2> INSERT INTO MyValues (MyValue) SELECT 3
3> INSERT INTO MyValues (MyValue) SELECT 4
4> INSERT INTO MyValues (MyValue) SELECT 4
5> INSERT INTO MyValues (MyValue) SELECT 4
6> INSERT INTO MyValues (MyValue) SELECT 5
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT VAR(MyValue) FROM MyValues
3> GO
------------------------
1.3333333333333333
(1 rows affected)
1>
2>
3> drop table MyValues;
4> GO
VAR computes the variance of all the values listed in a column or expression.
The aggregate function VARP computes the variance for the population of all the values listed in a column or expression.
11>
12> CREATE TABLE employee(
13> id INTEGER NOT NULL PRIMARY KEY,
14> first_name VARCHAR(10),
15> last_name VARCHAR(10),
16> salary DECIMAL(10,2),
17> start_Date DATETIME,
18> region VARCHAR(10),
19> city VARCHAR(20),
20> managerid INTEGER
21> );
22> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2> SELECT VAR(salary) variance_of_budgets
3> FROM employee
4>
5>
6>
7> drop table employee;
8> GO
variance_of_budgets
------------------------
755682.77777777612
(1 rows affected)
1>