This article will explain SQL Server average function, AVG(), and its use cases. Many times, we need to find the average value of numeric data stored in a column to address our distinct business requirements. SQL Server offers an aggregate function, AVG() to get the average value of the specified expression in its statement. This logic behind getting the average value using this function is first add all values and then divide the sum by the total number of values.

Syntax

Here is the syntax of the AVG() SQL function:

--Syntax
AVG ([ALL| DISTINCT] expression)

Here are the meanings for the arguments:

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

Let’s go and understand the various use cases of this function in the below section.

I have created a table in my test database and inserted few rows for this demonstration. The name of this table is Associates, which is storing the name, city, age, gender, designation, and salary of each employee. You can run the below T-SQL statement to create the table.

USE [TESTDB]
GO
CREATE TABLE [dbo].[Associates](
[ID] [int] NULL,
[name] [nchar](10) NULL,
[city] [nchar](10) NULL,
[age] [int] NULL,
[gender] [char](10) NULL,
[designation] [char](10) NULL,
[Salary] [money] NULL
) 

Next, you can insert some rows to this table using the below query.

USE [TESTDB]
GO
INSERT INTO [dbo].[Associates]
([ID] ,[name] ,[city] ,[age] ,[gender], [designation] ,[Salary])
VALUES
(1, 'Ram', 'Mumbai', 32, 'Male', 'Specialist', 80000),
(2, 'Shyam', 'New Delhi', 29,'Male','Specialist', 80000),
(3, 'Mary', 'London', 23, 'Female', 'Sr Analyst', 55000),
(4, 'Mark', 'Paris', 35, 'Male', 'Manager', 98000),
(5, 'Julie', 'Paris', 25, 'Female', 'Analyst', 60000),
(6, 'David', 'Hongkong', 41, 'Male', 'Sr Manager', 112000),
(7, 'Ankit', 'New Delhi', 29, 'Male', 'Sr Analyst', 76000),
(8, 'Suresh', 'New Delhi', 29, 'Male', 'Specialist', 80000),
(9, 'Jignesh', 'London', 27, 'Male', 'Analyst', 60000),
(10, 'Roger', 'Hongkong', 39, 'Male', 'Manager', 81000),
(11, 'Robert', 'London', 34, 'Male', 'Specialist', 80000),
(12, 'Rameshwar', 'New Delhi', 23, 'Male', 'Analyst', 60000),
(13, 'Rojer', 'Paris', 28, 'Male', 'Analyst', 60000),
(14, 'Shaam', 'Mumbai', 23, 'Male', 'Analyst', 60000),
(15, 'Julee', 'London', 21, 'Female', 'Analyst', 60000),
(16, 'Gignesh', 'New Delhi', 23, 'Male', 'Analyst', 60000),
(17, 'Zignesh', 'Paris', 39, 'Male', 'Manager', 84000)
GO

Finally, the data in your table will look like the one showing in the below image.

]

Use Cases of function AVG()

You will learn below the use cases of this SQL function which you can use in your queries to get the average values.

• Basic use case
• Use AVG() with DISTINCT
• Using AVG() with GROUP BY statement
• Use AVG() with WHERE clause

Let’s start with the first use case. You can launch SQL Server Management Studio or SQL manager for your development work. The dbForge Studio for SQL Server is very useful for SQL developers and DBAs, and they can perform complex database tasks using feature-rich GUI tools to speed up almost any database experience.

Basic use case

This section will explain a basic use case in which we will get the average salary that company is paying to their employees.

Here, I will run below SQL commands in which I have specified column salary as expression with this function AVG() to get the average salary paid to all employees. If you will notice, I have not used any argument in the below query. This means the query will act as if I used the argument, ALL, while displaying the result. I will also show similar result while mentioning the argument ALL later in this article.

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

I have executed the above statement, and its output is shown in the below image. Here, we can see that company is paying an average salary to their employees is $ 73294.11.

As I have stated above, the argument ALL is the default option. I have not mentioned ALL arguments in the above example and still, output has returned the average for all values. Let me show you this by running the below statements in which I have added one more output column with the argument ALL.

SELECT AVG(salary) AS [Average Salary],
       AVG(ALL salary) AS [Average Salary using ALL]
 FROM [dbo].[Associates]
GO

The output returned by both statements is the same in the below image.

We can further put conditions on the result using the WHERE clause to get the average value of a filtered result. For example, in the example above, I have calculated the average salary that the company is paying to all their employees. Now, let’s assume another requirement to compute the average salary of only female employees. In this case we will use the WHERE clause on the gender column, as I did in the below example. You can see the average salary of female employees is way lower than the average salary of the combined male and female workforce.

SELECT AVG(salary) AS [Average Salary - Female]
 FROM [dbo].[Associates]
 WHERE gender=’female’

The result of the above T-SQL statements is shown in the below image. This shows $58,333.33 instead of the $73,294.12 above (rounded values).

Use AVG() with DISTINCT

We have learned one of the arguments, ALL, in the previous section. Next, I will explain another argument, DISTINCT. The argument DISTINCT considers only distinct values to calculate the average value. If the same value is defined many times, the function will pick only one entry of each unique value and then calculate the average value. Let’s understand this with the help of the below example.

I have used the DISTINCT argument in the below statement to get the average salary of all distinct values.

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

We can see the output is showing as different than the output returned in the above section for all entries. Here, the average salary is returned as $80,750, whereas the average salary of all employees was only $73,294.11. This is because we are ignoring some duplicate values of lower salaries that lower the average.

You can use these arguments ALL and DISTINCT in a single query as well to get the desired result for all values or for all distinct values.

Use AVG() with GROUP BY statement

This section will explain the use case of SQL function AVG() with GROUP BY statement. I have shown you in the first section that the average salary of female workers is less than the average salary of all employees, so the company has decided to see the average salary of both male and female employees. We will use the GROUP BY statement with this function to get this information.

Here, I have used column gender in the GROUP BY statement. I have also fetched the total salary paid to all female workers and make workers along with their respective average salary.

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

The result shows both male and female employees' average salary, and total salary paid in the below result set. We can see the difference between male and female average salaries. Male average salary is more than the female employees. Here, I have also shown you the use case of using this SQL function with another useful function SUM in the above example.

You can also get the total count of male or female employees on which this average salary has been calculated. We can add another SQL function COUNT to get the count of all male and female employees.

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

I have executed the above SQL commands and see the output in the below image. The company has only 3 female employees whereas they have a total of 14 male employees whose average salary is shown in the below result.

g

Use AVG() with a WHERE clause

We can also use SQL function AVG() within WHERE clause for condition-based filtering. Suppose we want to know the list of all employees getting paid more than the average salary this company is paying. We will filter all employees based on conditions defined in the WHERE clause using SQL function AVG() . I have added a WHERE clause statement on column salary as given in the below code.

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

Once I had executed the above statements, I got the below result set which shows a list of all employees who are getting paid more than the average salary of $ 73294.11.

We can also use this function along with the WHERE clause and GROUP BY statement together. If you only need a count of total employees based on their gender, you can use this WHERE clause condition in the same statement I gave in the GROUP BY use case.

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

Here, we can see 9 male employees are getting a salary more than the average company salary. We can also see their average and total salary paid to them.

Conclusion

Today you have learned SQL function AVG() and its use cases with various other statements in this article. Please share your feedback in the comment section. Your comments help us improve in a better way.