SQL AVG FUNCTION 4

SQL AVG Function

The SQL AVG Function calculates the Average of total selected records or rows. The syntax of the AVG function to calculate the 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 function example, we use this data.

Customer Table for Aggregate Functions

TIP: The will only work on Numeric Columns.

SQL AVG Function 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]
Query Returns Average Salary of an Employee 1

SQL AVG Group By Clause Example

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 AVG Function to calculate the average of products present in each group. Let us see the 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 Distinct Function Example

The AVG DISTINCT Column calculates the average of the Unique number of records whose values are NOT NULL (Ignores the NULL Records). The 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 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 example, see that the Average Yearly Income is 67000. So above math SQL Server AVG query will display all the customers whose yearly income is greater than 67000

SQL AVG FUNCTION 4