SQL VARP Function

The SQL VARP Function is one of the SQL Aggregate Function, which is used to calculate the Statistical Variance for the population of total records (or rows) selected by the  SQL SELECT Statement.

TIP: VARP Function will only work on Numeric Columns and it ignores Nulls.

SQL VARP Function Formula

The mathematical formulas behind the VARP function to calculate the Statistical Variance for the population in SQL Server is as shown below:

SQL VARP Function Syntax

The basic syntax of the SQL VARP Function in SQL Server is as shown below:

In this article we will show you, How to write VARP Function in SQL Server 2014 with example. For this, We are going to use the below shown data

SQL VARP Example

VARP() simply returns the Variance  for the population 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

OUTPUT

SQL VARP Function 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 GROUP BY Clause to group the products by color or category and then use the VARP Function to calculate the variance for population of products present in each group. Let us see the Example

T-SQL CODE

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.

SQL VARP Function 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

Below lines of code will check whether the aggregated amount (Variance for population of Yearly income for each individual Group) is greater than 0 or not. If this is True then corresponding records will be displayed.

OUTPUT

Thank You for Visiting Our Blog