The SQL VAR Function is one of the SQL Aggregate Function, which is used to calculate the statistical Variance of total records (or rows) selected by the SQL SELECT Statement.
TIP: VAR Function will only work on Numeric Columns and it ignores Nulls.
SQL Var Function Formula
The mathematical formulas behind the VAR function to calculate the Statistical Variance in SQL Server 2014 is as shown below:
|
1 2 3 4 5 |
--Calculating the Mean or Average Mean = Sum of each individual/Total number of items --Calculating the Statistical Variance Variance = ((OriginalValue – Mean)² + (OriginalValue – Mean)² +.... )/(Total number of items - 1) |
SQL Var Function Syntax
The basic syntax of the VAR Function in SQL Server is as shown below:
|
1 2 |
SELECT VAR ([Column_Name]) FROM [Source] |
In this article we will show you, How to write Var Function in SQL Server 2014 with example. For this, We are going to use the below shown data
SQL VAR Example
The SQL Server VAR simply returns the Variance of total number of records present in the specified column. For example, The following query will calculate the variance of total records present in the [Yearly Income] column from Customers table.
T-SQL CODE
|
1 2 3 4 |
-- Example for SQL Server VAR Function SELECT VAR ([YearlyIncome]) AS [Variance Income] FROM [Customer] |
OUTPUT
SQL VAR with Group By Clause
In most cases we usually calculate the variance of a products belongs to particular category or color etc. In these situations we use SQL GROUP BY Clause to group the products by color or category and then use the Var Function to calculate the variance of products present in each group. Let us see the Example
T-SQL CODE
|
1 2 3 4 5 |
-- Example for SQL Server VAR Function SELECT [Occupation] ,VAR ([YearlyIncome]) AS [Income Variance] FROM [Customer] GROUP BY [Occupation] |
Above SQL Query will find, the Customers associated with the particular Department and calculates their income variance
OUTPUT
ANALYSIS
It is too big to show the calculations for every thing so, We are taking Skilled Manual profession and show you the output.
|
1 2 3 4 5 6 7 8 9 |
--Calculating Mean Mean = (60000 + 80000) / 2 Mean = 70000 --Calculating Variance Variance = ( (60000 - 70000) + (80000 - 70000) ) / (2 -1) Variance = 200000000 |
SQL VAR in Having Clause
When we are grouping the data, In some cases we usually check for the conditions against the aggregated data. In these situations we use SQL HAVING Clause along with Group By Statement. For example, The following query will group the Customers by their Occupation and then finds the Income variance of each individual group.
T-SQL CODE
|
1 2 3 4 5 6 |
-- Example for SQL Server VAR Function SELECT [Occupation] ,VAR ([YearlyIncome]) AS [Income Variance] FROM [Customer] GROUP BY [Occupation] HAVING VAR ([YearlyIncome]) > 0 |
Below lines of code will check whether the aggregated amount (Variance of Yearly income for each individual Group) is greater than 0 or not. If this is True then corresponding records will be displayed.
|
1 |
HAVING VAR ([YearlyIncome]) > 0 |
OUTPUT
Thank You for Visiting Our Blog
Leave a Reply