SQL AVG FUNCTION 4

SQL AVG Function

by

The SQL AVG Function calculates 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 use this data

SQL AVG FUNCTION

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. 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]
SQL AVG FUNCTION 1

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

SQL AVG FUNCTION 2

SQL AVG Function Distinct

The SQL Server AVG DISTINCT Column calculates the average of the Unique number of records whose values are NOT NULL (Ignores the NULL Records). The SQL DISTINCT removes the Duplicates from the given column.

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

It finds 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 FUNCTION 3

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, this SQL Server avg query will return 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, see that the Average Yearly Income is 67000. So above math avg query will display all the customers whose yearly income is greater than 67000

SQL AVG FUNCTION 4