SQL AVG Function

The SQL AVG Function is one of the SQL Aggregate Function, which is used to calculate the Average of total records (or rows) selected by the SQL 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: The AVG Function will only work on Numeric Columns.

SQL Avg Function Syntax

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

SELECT AVG ([Column_Name])
FROM [Source]

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

SQL AVG Example

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

T-SQL CODE

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

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

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

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