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 SQL server Avg function.
SQL Avg Function Syntax
The basic syntax of the AVG in SQL Server to calculate average is as shown below:
SELECT AVG ([Column_Name]) FROM [Source]
TIP: The SQL Server AVG Function will only work on Numeric Columns.
In this article we will show you, How to write Avg in SQL Server to find average with an example. For this, We are going to use the below shown data
SQL AVG Example
The SQL server Avg function 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.
SELECT AVG ([YearlyIncome]) AS [Total Income] FROM [Customer]
SQL AVG 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 sql Avg Function to calculate the average of a products present in each group. Let us see the Example
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
SQL AVG Function Distinct
The Sql Server 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.
SELECT [Occupation] ,AVG (DISTINCT [YearlyIncome]) AS [Total Income] FROM [Customer] GROUP BY [Occupation]
Above 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
SQL Avg in SubQuery
For example, The following Sql Server avg query will return all the Customers present in the customers table whose [Yearly Income] is greater than the average yearly income.
SELECT [FirstName] ,[LastName] ,[YearlyIncome] ,[Education] ,[Occupation] FROM [Customer] WHERE [YearlyIncome] > (SELECT AVG([YearlyIncome]) FROM [Customer])
From the first Sql Server avg function 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