Aggregate Functions

SQL AVG Function

2015-06-27 by suresh

In SQL Server, AVG Function is used to calculate the Average of total records (or rows) selected by the  SELECT Statement. For example, If you want to find the Average price of a products present in your Store or you want to find the Average Cost of a Black color products present in your Store, you can simply use the Avg function.

TIP: AVG Function will only work on Numeric Columns.

SQL Avg Function Syntax

In SQL Server, The basic syntax of the SQL AVG() Function is:

1 2	SELECT AVG ([Column_Name]) FROM [Source]

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

SQL AVG() Example

Avg () simply returns the Average of a total number of records present in the specified column. For example, The following query will calculate the Average of total records present in the [Yearly Income] column from Customers table.

T-SQL CODE

1 2 3

SELECT AVG ([YearlyIncome]) AS [Total Income]          FROM [Customer]

OUTPUT

SQL AVG Function with Group By Clause

In most cases we usually check for Average product price that 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 Avg Function to calculate the average of a products present in each group. Let us see the Example

T-SQL CODE

1 2 3 4

SELECT [Occupation]        ,AVG ([YearlyIncome]) AS [Total Income]       FROM [Customer]   GROUP BY [Occupation]

Above SQL Query will find, the Customers associated with the particular Department and finds the average income of each department

OUTPUT

SQL AVG Function with Distinct

AVG (DISTINCT Column Name) simply calculate the average of the Unique number of records present in the table whose values are NOT NULL (Ignores the NULL Records).

TIP: In SQL, DISTINCT Keyword is used to remove the Duplicates from the specified column Name.

T-SQL CODE

1 2 3 4

SELECT [Occupation]        ,AVG (DISTINCT [YearlyIncome]) AS [Total Income]     FROM [Customer] GROUP BY [Occupation]

Above SQL Query will find the Unique number of records (by removing duplicates) present in the [Yearly Income] column whose values are NOT NULL and then calculates the average of them

OUTPUT

SQL Avg Function in SubQuery

When we are retrieving the data, In some cases we usually check for the conditions against the aggregated data. In these situations we have to use Sub queries in Where Clause. For example, The following query will return all the Customers present in the customers table whose [Yearly Income] is greater than the average yearly income.

T-SQL CODE

1 2 3 4 5 6 7 8

SELECT [FirstName]       ,[LastName]       ,[YearlyIncome]       ,[Education]       ,[Occupation]   FROM [Customer]   WHERE [YearlyIncome] >         (SELECT AVG([YearlyIncome]) FROM [Customer])

From the first example you can observe that, Average Yearly Income is 67000. So above query will display all the customers from the Customers table whose yearly income is greater than 67000

OUTPUT

Thank You for Visiting Our Blog

SQL COUNT Function

2015-06-16 by suresh

In SQL Server, COUNT Function is used to Count the number of records (or rows) selected by the  SELECT Statement. For example, If you want to find the total Number of products present in your Store or you want to find the Black color products present in your Store. In these situations you can simply use the COUNT function.

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

SQL COUNT (*) Example

COUNT(*) simply returns the total number of records present in the table. Let us see one example to understand it better

T-SQL CODE

1 2 3

SELECT COUNT (*) AS [Total Records]    FROM [Employee]

Above SQL Query will count the total number of records present in the Employee table

OUTPUT

NOTE: The COUNT(*) counts NULL values and Duplicate records as well.

SQL COUNT (Column Name) Example

COUNT(Column Name) simply returns the total number of records present in the table whose values are NOT NULL (Ignores the NULL Records). Let us see one example

NOTE: The COUNT(Column Name) counts the Duplicate records.

T-SQL CODE

1 2 3

SELECT COUNT ([FirstName]) AS [Total Records]    FROM [Employee]

OUTPUT

We got the same result as COUNT(*) Query because there are no NULL Values in the [FirstName] Column. Let us use another Column

T-SQL CODE

1 2 3

SELECT COUNT ([DepartID]) AS [Total Records]    FROM [Employee]

Above SQL Query will count the total number of records present in the [DepartID] whose values are NOT NULL

OUTPUT

SQL COUNT DISTINCT Example

COUNT(DISTINCT Column Name) simply returns the Unique number of records present in the table whose values are NOT NULL (Ignores the NULL Records).

TIP: In SQL, DISTINCT Keyword is used to remove the Duplicates from the specified column Name.

T-SQL CODE

1 2 3

SELECT COUNT (DISTINCT [DepartID]) AS [Total Records]    FROM [Employee]

Above SQL Query will count the Unique number of records (by removing duplicates) present in the [DepartID] whose values are NOT NULL

OUTPUT

SQL COUNT along with GROUP BY Clause

In most cases we usually check for number of 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 COUNT Function to count the number of products present in that group. Let us see the Example

T-SQL CODE

1 2 3 4

SELECT [DepartID],       COUNT ([FirstName]) AS [Total Records] FROM [Employee] GROUP BY [DepartID]

Above SQL Query will find, How many Employees are associated with the particular Department ID.

OUTPUT

From the above screenshot you can observe that, SQL Query is grouping the NULL records and count them as well. Let us use the WHERE Clause with NOT NULL and check again the result

OUTPUT

TIP: Use NOT NULL to avoid the NULL records in the query

Thank You for Visiting Our Blog

SQL Max Function

2015-06-27 by suresh

In SQL Server, MAX Function is used to find the Maximum value from the total records (or rows) selected by the SELECT Statement.

TIP: MAX Function will ignore Null values.

SQL Max Function Syntax

In SQL Server, The basic syntax of the SQL Max() Function is:

1 2	SELECT MAX ([Column_Name]) FROM [Source]

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

SQL MAX() Example

Max() simply returns the Maximum value from the total records present in the specified column. For example, The following query will find the Maximum yearly income present in the [Yearly Income] column from Customers table.

T-SQL CODE

1 2 3

SELECT MAX ([YearlyIncome]) AS [Minimum Income]          FROM [Customer]

OUTPUT

SQL MAX Function with Group By Clause

In most cases we usually check maximum product price (Highest pricing product) 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 Max Function to find the Highest pricing product present in each group. Let us see the Example

T-SQL CODE

1 2 3 4

SELECT [Occupation]        ,MAX ([YearlyIncome]) AS [Minimum Income]     FROM [Customer] GROUP BY [Occupation]

Above SQL Query will find, the Customers associated with the particular Department and finds the maximum income of a customer present in each department

OUTPUT

SQL Max Function with 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 HAVING Clause along with Group By Statement. For example, The following query will group the Customers by their Occupation and then finds the Maximum yearly Income present in each group.

T-SQL CODE

1 2 3 4 5

SELECT [Occupation]        ,MAX ([YearlyIncome]) AS [Minimum Income]     FROM [Customer] GROUP BY [Occupation] HAVING MAX ([YearlyIncome]) > 50000

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

1	HAVING MAX ([YearlyIncome]) > 50000

OUTPUT

SQL Max Function in Sub Query

When we are retrieving the data, In some cases we usually check for the conditions against the aggregated data. In these situations we have to use Sub queries in Where Clause. For example, The following query will return all the Customers present in the customers table whose [Yearly Income] is exactly equal to the Maximum yearly income.

T-SQL CODE

1 2 3 4 5 6 7 8

SELECT [FirstName]       ,[LastName]       ,[YearlyIncome]       ,[Education]       ,[Occupation]   FROM [Customer]   WHERE [YearlyIncome] =         (SELECT MAX([YearlyIncome]) FROM [Customer])

From the first example you can observe that, Maximum Yearly Income is 90000. So above query will display all the customers from the Customers table whose yearly income is equal to 90000

OUTPUT

Thank You for Visiting Our Blog

SQL Min Function

2015-06-27 by suresh

In SQL Server, MIN Function is used to find the Minimum value from the total records (or rows) selected by the SELECT Statement.

TIP: MIN Function will ignore Null values.

SQL Min Function Syntax

In SQL Server, The basic syntax of the SQL MIN() Function is:

1 2	SELECT MIN ([Column_Name]) FROM [Source]

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

SQL MIN() Example

Min() simply returns the Minimum value from the total records present in the specified column. For example, The following query will find the Minimum yearly income present in the [Yearly Income] column from Customers table.

T-SQL CODE

1 2 3

SELECT MIN ([YearlyIncome]) AS [Minimum Income]          FROM [Customer]

OUTPUT

SQL MIN Function with Group By Clause

In most cases we usually check minimum product price (Lowest pricing product) 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 Min Function to find the Lowest pricing product present in each group. Let us see the Example

T-SQL CODE

1 2 3 4

SELECT [Occupation]        ,MIN ([YearlyIncome]) AS [Minimum Income]     FROM [Customer] GROUP BY [Occupation]

Above SQL Query will find, the Customers associated with the particular Department and finds the minimum income of a customer present in each department

OUTPUT

SQL Min Function with 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 HAVING Clause along with Group By Statement. For example, The following query will group the Customers by their Occupation and then finds the Minimum yearly Income present in each group.

T-SQL CODE

1 2 3 4 5

SELECT [Occupation]        ,MIN ([YearlyIncome]) AS [Minimum Income]     FROM [Customer] GROUP BY [Occupation] HAVING MIN ([YearlyIncome]) > 50000

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

1	HAVING MIN ([YearlyIncome]) > 50000

OUTPUT

SQL Min Function in Sub Query

When we are retrieving the data, In some cases we usually check for the conditions against the aggregated data. In these situations we have to use Sub queries in Where Clause. For example, The following query will return all the Customers present in the customers table whose [Yearly Income] is greater than the Minimum yearly income.

T-SQL CODE

1 2 3 4 5 6 7 8

SELECT [FirstName]       ,[LastName]       ,[YearlyIncome]       ,[Education]       ,[Occupation]   FROM [Customer]   WHERE [YearlyIncome] >         (SELECT MIN([YearlyIncome]) FROM [Customer])

From the first example you can observe that, Minimum Yearly Income is 50000. So above query will display all the customers from the Customers table whose yearly income is greater than 50000

OUTPUT

Thank You for Visiting Our Blog

SQL STDEV Function

2015-06-28 by suresh

In SQL Server, STDEV Function is used to calculate the standard Deviation of total records (or rows) selected by the SELECT Statement.

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

SQL STDEV Function Formula

1 2 3 4 5 6 7 8

--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)   --Calculating Standard Deviation Standard Deviation = Square root (Variance)

SQL STDEV Function Syntax

In SQL Server, The basic syntax of the SQL STDEV() Function is:

1 2	SELECT STDEV ([Column_Name]) FROM [Source]

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

SQL STDEV() Example

STDEV () simply returns the Standard Deviation of total number of records present in the specified column. For example, The following query will calculate the Standard Deviation of total records present in the [Yearly Income] column from Customers table.

T-SQL CODE

1 2 3

SELECT STDEV ([YearlyIncome]) AS [Standard Deviation]          FROM [Customer]

OUTPUT

SQL STDEV Function with Group By Clause

In most cases we usually calculate the Standard Deviation 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 STDEV Function to calculate the Standard Deviation of products present in each group. Let us see the Example

T-SQL CODE

1 2 3 4

SELECT [Occupation]        ,STDEV ([YearlyIncome]) AS [Standard Deviation]       FROM [Customer]   GROUP BY [Occupation]

Above SQL Query will find, the Customers associated with the particular Department and calculates their Standard Deviation

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 10 11 12

--Calculating Mean Mean = (60000 + 80000) / 2 Mean = 70000   --Calculating Variance Variance = ( (60000 - 70000) + (80000 - 70000) ) / (2 -1) Variance = 200000000   --Calculating Standard Deviation Standard Deviation = SQRT (Variance) Standard Deviation = SQRT (200000000) Standard Deviation = 14142.13

SQL STDEV 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 HAVING Clause along with Group By Statement. For example, The following query will group the Customers by their Occupation and then calculates the Standard Deviation of each individual group.

T-SQL CODE

1 2 3 4 5

SELECT [Occupation]        ,STDEV ([YearlyIncome]) AS [Standard Deviation]     FROM [Customer] GROUP BY [Occupation] HAVING STDEV ([YearlyIncome]) > 0

Below lines of code will check whether the aggregated amount (Standard Deviation 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 STDEV ([YearlyIncome]) > 0

OUTPUT

SQL STDEV Function in Sub Query

When we are retrieving the data, In some cases we usually check for the conditions against the aggregated data. In these situations we have to use Sub queries in Where Clause. For example, The following query will return all the Customers present in the customers table whose [Yearly Income] is greater than five times yearly incomes Standard Deviation.

T-SQL CODE

1 2 3 4 5 6 7 8

SELECT [FirstName]       ,[LastName]       ,[YearlyIncome]       ,[Education]       ,[Occupation]   FROM [Customer]   WHERE [YearlyIncome] >         (SELECT STDEV([YearlyIncome] * 5) FROM [Customer])

From the first example you can observe that, Standard Deviation of a Yearly Income is 12516. So above query will display all the customers from the Customers table whose yearly income is greater than 62580 approximately

OUTPUT

Thank You for Visiting Our Blog

SQL STDEVP Function

2015-06-28 by suresh

In SQL Server, STDEVP Function is used to calculate the standard Deviation for population of total records (or rows) selected by the SELECT Statement.

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

SQL STDEVP Function Formula

1 2 3 4 5 6 7 8

--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   --Calculating Standard Deviation Standard Deviation = Square root (Variance)

SQL STDEVP Function Syntax

In SQL Server, The basic syntax of the STDEVP() Function is:

1 2	SELECT STDEVP ([Column_Name]) FROM [Source]

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

SQL STDEVP() Example

STDEVP () simply returns the Standard Deviation for population of total number of records present in the specified column. For example, The following query will calculate the Standard Deviation for population of total records present in the [Yearly Income] column from Customers table.

T-SQL CODE

1 2 3

SELECT STDEVP ([YearlyIncome]) AS [Standard Deviation]          FROM [Customer]

OUTPUT

SQL STDEVP Function with Group By Clause

In most cases we usually calculate the Standard Deviation for population 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 STDEV Function to calculate the Standard Deviation of products present in each group. Let us see the Example

T-SQL CODE

1 2 3 4

SELECT [Occupation]        ,STDEVP ([YearlyIncome]) AS [Standard Deviation]       FROM [Customer]   GROUP BY [Occupation]

Above SQL Query will find, the Customers associated with the particular Department and calculates their Standard Deviation for population.

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 10 11 12

--Calculating Mean Mean = (60000 + 80000) / 2 Mean = 70000   --Calculating Variance Variance = ( (60000 - 70000) + (80000 - 70000) ) / 2 Variance = 100000000   --Calculating Standard Deviation Standard Deviation = SQRT (Variance) Standard Deviation = SQRT (100000000) Standard Deviation = 10000

SQL STDEVP 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 HAVING Clause along with Group By Statement. For example, The following query will group the Customers by their Occupation and then calculates the Standard Deviation for population of each individual group.

T-SQL CODE

1 2 3 4 5

SELECT [Occupation]        ,STDEVP ([YearlyIncome]) AS [Standard Deviation]     FROM [Customer] GROUP BY [Occupation] HAVING STDEVP ([YearlyIncome]) > 0

Below lines of code will check whether the aggregated amount (Standard Deviation 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 STDEVP ([YearlyIncome]) > 0

OUTPUT

SQL STDEVP Function in Sub Query

When we are retrieving the data, In some cases we usually check for the conditions against the aggregated data. In these situations we have to use Sub queries in Where Clause. For example, The following query will return all the Customers present in the customers table whose [Yearly Income] is greater than five times yearly incomes Standard Deviation for population.

T-SQL CODE

1 2 3 4 5 6 7 8

SELECT [FirstName]       ,[LastName]       ,[YearlyIncome]       ,[Education]       ,[Occupation]   FROM [Customer]   WHERE [YearlyIncome] >         (SELECT STDEVP([YearlyIncome] * 6) FROM [Customer])

From the first example you can observe that, Standard Deviation of a Yearly Income is 11874. So above query will display all the customers from the Customers table whose yearly income is greater than 71244 approximately

OUTPUT

Thank You for Visiting Our Blog

SQL SUM Function

2015-06-27 by suresh

In SQL Server, SUM Function is used to calculate the total or Sum of records (or rows) selected by the  SELECT Statement. For example, If you want to find the total price of a products present in your Store or you want to find the total price of a Black color products present in your Store you can simply use the Sum function.

TIP: SUM Function will only work on Numeric Columns.

SQL Sum Function Syntax

In SQL Server, The basic syntax of the SQL Sum() Function is:

1 2	SELECT SUM ([Column_Name]) FROM [Source]

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

SQL SUM() Example

Sum () simply returns the sum of total number of records present in the specified column. For example, The following query will calculate the sum of total records present in the [Yearly Income] column from Customers table.

T-SQL CODE

1 2 3

SELECT SUM ([YearlyIncome]) AS [Total Income]          FROM [Customer]

OUTPUT

SQL SUM Function with Group By Clause

In most cases we usually Total the 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 Sum Function to Sum the products present in each group. Let us see the Example

T-SQL CODE

1 2 3 4

SELECT [Occupation]        ,SUM ([YearlyIncome]) AS [Total Income]       FROM [Customer]   GROUP BY [Occupation]

Above SQL Query will find, the Customers associated with the particular Department and total their income

OUTPUT

SQL SUM Function with Distinct

SUM (DISTINCT Column Name) simply calculate the sum of the Unique number of records present in the table whose values are NOT NULL (Ignores the NULL Records).

TIP: In SQL, DISTINCT Keyword is used to remove the Duplicates from the specified column Name.

T-SQL CODE

1 2 3 4

SELECT [Occupation]        ,SUM (DISTINCT [YearlyIncome]) AS [Total Income]     FROM [Customer] GROUP BY [Occupation]

Above SQL Query will Sum the Unique number of records (by removing duplicates) present in the [Yearly Income] column whose values are NOT NULL

OUTPUT

SQL Sum Function with 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 HAVING Clause along with Group By Statement. For example, The following query will group the Customers by their Occupation and then finds the Total Income.

T-SQL CODE

1 2 3 4 5

SELECT [Occupation]        ,SUM ([YearlyIncome]) AS [Total Income]     FROM [Customer] GROUP BY [Occupation] HAVING SUM ([YearlyIncome]) >= 200000

Below lines of code will check whether the aggregated amount (Sum of Yearly income of each individual Group) is greater than or equal to 200000 or not. If this is True then corresponding records will be displayed.

1	HAVING SUM ([YearlyIncome]) >= 200000

OUTPUT

Thank You for Visiting Our Blog

SQL VAR Function

2015-06-28 by suresh

In SQL Server, VAR Function is used to calculate the statistical Variance of total records (or rows) selected by the  SELECT Statement.

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

SQL Var Function Formula

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

In SQL Server, The basic syntax of the SQL Var() Function is:

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

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

SELECT VAR ([YearlyIncome]) AS [Variance Income]          FROM [Customer]

OUTPUT

SQL Var 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 Var Function to calculate the variance of products present in each group. Let us see the Example

T-SQL CODE

1 2 3 4

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 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 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

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

SQL VARP Function

2015-06-28 by suresh

In SQL Server, VARP Function is used to calculate the statistical Variance for the population of total records (or rows) selected by the  SELECT Statement.

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

SQL VARP Function Formula

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

SQL VARP Function Syntax

In SQL Server, The basic syntax of the SQL VARP() Function is:

1 2	SELECT VARP ([Column_Name]) FROM [Source]

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

1 2 3

SELECT VARP ([YearlyIncome]) AS [Income Variance]          FROM [Customer]

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

1 2 3 4

SELECT [Occupation]        ,VARP ([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 for Population Variance for population = ( (60000 - 70000) + (80000 - 70000) ) / 2   Variance for population = 100000000

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 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

SELECT [Occupation]        ,VARP ([YearlyIncome]) AS [Income Variance]     FROM [Customer] GROUP BY [Occupation] HAVING VARP ([YearlyIncome]) > 0

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.

1	HAVING VARP ([YearlyIncome]) > 0

OUTPUT

Thank You for Visiting Our Blog