SQL AVG Function

The SQL AVG Function is a SQL Aggregate Function used to calculate the Average of total selected records or rows. The syntax of the AVG in SQL Server to calculate average is

SELECT AVG ([Column_Name])
FROM [Source]

For example, If you want to find the average price of products present in your Store. Or you want to find the Average Cost of Black color products, use the SQL server Avg function.

For this SQL Server AVG function example, we are going to use the below shown data

TIP: The SQL Server AVG Function will only work on Numeric Columns.

SQL AVG Example

The SQL server Avg function returns the Average of a total number of records present in the specified column.

For example, The following Aggregate Function query will calculate the Average of total records present in the [Yearly Income] column from the Customers table.

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

OUTPUT

SQL AVG Group By Clause

In general, we check for the Average product price belongs to a selective color. In this case, we use GROUP BY Clause to group the products by color. Then use the SQL AVG Function to calculate the average of products present in each group. Let us see the AVG Group By Example

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

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

OUTPUT

SQL AVG Function Distinct

The SQL Server AVG DISTINCT Column calculates the average of the Unique number of records present in the table whose values are NOT NULL (Ignores the NULL Records).

TIP: The SQL DISTINCT removes the Duplicates from the given 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

OUTPUT

SQL Avg in Subquery

When we are retrieving the data, In some cases, we use the conditions against the aggregated data. In these situations, we have to use Subquery in Where Clause.

For example, the below-shown SQL Server avg query will return all the Customers 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 AVG function example, you can observe that the Average Yearly Income is 67000. So above query will display all the customers from the Customers table whose yearly income is greater than 67000