In SQL Server, there are times when we need to find the average value of numeric data stored in a column to address specific business requirements. To achieve this, SQL Server provides an aggregate function called AVG(). The AVG() function calculates the average value of the specified expression in a SQL statement. The logic behind this function is to add all values and then divide the sum by the total number of values.

Here is the syntax of the AVG() function:

AVG ([ALL| DISTINCT] expression)

The arguments for the AVG() function are as follows:

• ALL: This is the default argument and considers all values to calculate the average. It is not mandatory to mention it while using this function.
• DISTINCT: This argument considers only distinct values to calculate the average. If a number is specified multiple times, the AVG() function will consider it only once while calculating the average.
• Expression: This is the numeric data type category.

Let’s explore some use cases of the AVG() function:

Basic Use Case

In this use case, we will calculate the average salary that a company is paying to its employees. Here is an example query:

SELECT AVG(salary) AS [Average Salary]
FROM [dbo].[Associates]

This query will return the average salary paid to all employees. The result will be displayed as the average salary.

Use AVG() with DISTINCT

The DISTINCT argument in the AVG() function considers only distinct values to calculate the average. Here is an example query:

SELECT AVG(DISTINCT salary) AS [Average Salary]
FROM [dbo].[Associates]

This query will return the average salary of all distinct values. It will ignore duplicate values and calculate the average based on unique values.

Use AVG() with GROUP BY statement

The AVG() function can be used with the GROUP BY statement to calculate the average salary of different groups. Here is an example query:

SELECT gender, AVG(salary) AS [Average Salary], SUM(salary) AS [Total Salary Paid]
FROM [dbo].[Associates]
GROUP BY gender

This query will return the average salary and total salary paid for each gender. It provides insights into the average salary difference between male and female employees.

Use AVG() with WHERE clause

The AVG() function can also be used within the WHERE clause for condition-based filtering. Here is an example query:

SELECT name, city, age, gender, designation, salary
FROM [dbo].[Associates]
WHERE salary > (SELECT AVG(salary) FROM [dbo].[Associates])

This query will return a list of employees who are getting paid more than the average salary of the company.

In conclusion, the AVG() function in SQL Server is a powerful tool for calculating average values. It can be used in various scenarios to analyze data and make informed decisions. By understanding the different use cases of the AVG() function, you can leverage its capabilities to meet your specific business requirements.

Please feel free to share your feedback in the comments section. Your comments help us improve and provide better content.