SQL AVG FUNCTION 4

SQL AVG Function

The SQL Server AVG Function calculates the Average of total selected records or rows. The AVG function will only work on Numeric Columns, and its syntax is shown below.

SELECT AVG ([Column_Name])
FROM [Source]

For example, If you want to find the average price of products present in your Store. If 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

SQL Server AVG Function Example

The Avg function returns the average of the 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

Group By Clause Example

In general, we check for the Average product price belonging to a selective color. In this case, we use the 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 Example.

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

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

SQL AVG FUNCTION and Group By 2

SQL AVG Distinct Function Example

The 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

DISTINCT Example 3

SQL AVG Function in Where Clause 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 the 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 query will display all the customers whose yearly income is greater than 67000

SQL AVG FUNCTION in SUBQUERY 4