Problem
If you want to learn how to get an average value from a group of values using a system function in Microsoft SQL Server, then you should keep reading this SQL tutorial. SQL Server offers an aggregate function AVG() to calculate the average value of an expression. Today we will discuss this SQL function along with several examples.
Solution
Microsoft SQL Server provides various aggregate functions to perform numerical calculations. The T-SQL function AVG() is one of them that returns the average value of a group. If there are any NULL values present in the SQL data, then these values will be ignored by this function. The logic behind getting this average value is simple, we calculate by adding all values and then divide the sum by the number of values. If the sum exceeds the maximum number of the data type, then you will get an error in the output.
SQL AVG() Function Syntax
The syntax of this function is below:
--Syntax AVG ([ALL| DISTINCT] expression)
- ALL argument will consider all values of the expression to get the average value. This is the default option.
- DISTINCT argument will consider only distinct values to get the average.
- Expression is a group name like a column name of a numeric data type which will be specified in this function.
Source Data for Examples
Now, let’s understand the source table on which I will be showcasing for all the use cases. I have a table named OrderDetails which has all order and sales related information. You can see its columns and all the records in the below screenshot.
Let’s start the various use cases of this function in the below section.
SQL AVG using ALL and DISTINCT arguments
The first use case is about getting an average sales price of all products with a SELECT statement. As I mentioned above, the table OrderDetails stores sales related information in which each product, its price, sales person, month, and city are stored. Let’s get the average sales price by using this SQL function AVG on column Price where the cost of each product that is sold is shown.
SELECT AVG(Price) AS [Avg Price]
FROM [dbo].[OrderDetails]
I got the below output. Now, we know the average sales price is $1755.8214.
The above example is a basic use case of this function. Next, let’s use both arguments ALL and DISTINCT and see the output. I have already used statements without using any arguments, so the AVG function will use ALL as this is the default option and the result for argument ALL and the above statement must be the same.
Let me use all 3 AVG statements without any argument, with ALL and DISTINCT arguments in one T-SQL script, and get the result in a single output.
We can see the output returned by using ALL and without using ALL are the same in the below image, whereas the result returned by the DISTINCT argument is different because it is considering only distinct values for all duplicate entries.
SQL AVG with GROUP BY statement
This section will explain a use case of the AVG function with SQL GROUP BY logic in a SELECT statement.
Let’s continue the above example. We got the average price per invoice in the above section. Now, suppose you want to know the product-wise average price along with its total product count. We can get this information by using the SQL GROUP BY statement.
I have grouped all products and returned the average price for each product that has been sold to customers. I have also returned the total sales of that product along with these details by running the below T-SQL statement in the following query.
SELECT ProductName,
COUNT(ProductName) AS [Product count],
AVG(Price) AS [Avg Price],
SUM(Price) AS [Total Sales]
FROM [dbo].[OrderDetails]
GROUP BY ProductName
We can see the output of the above statements in the below screenshot. Now, you can see the average price of each product along with its total count and sales.
A different perspective of the above statement can also be returned by changing the column name used by the GROUP BY statement. I have grouped ProductName in the above example whereas I have grouped salesperson names in the below example. Here, we can get to know which salesperson has sold how many products along with their total and average sales.
SQL AVG with ORDER BY statement
Here we will explain the use case of the AVG function with the ORDER BY statement in the following example. Again, I will continue the above example. I have added the ORDER BY statement in the above example on average price. Here is the SQL statement I executed to get the result in descending order.
SELECT POC,
COUNT(ProductName) AS [Product count],
AVG(Price) AS [Avg Price],
SUM(Price) AS [Total Sales]
FROM [dbo].[OrderDetails]
GROUP BY POC
ORDER BY [Avg Price] DESC
The output of the above statements is shown in the below screenshot. Here we can see the result is shown in descending order of the [Avg Price] column. This is because of the ORDER BY statement.
There is another aspect or representation of the above output shown in the below example where I have applied the ORDER BY statement on the [Product Count] column.
SQL AVG with WHERE clause
We can also use the SQL AVG function in the WHERE clause for conditional data filtering. Suppose we want to get a list of all products which have been sold below the average price of all products each month. Here, we will use the AVG function in the WHERE clause to filter only those products which have been sold below the average price.
SELECT ProductName, Price, InvoiceMonth
FROM [dbo].[OrderDetails]
WHERE Price < (SELECT AVG(Price) FROM [dbo].[OrderDetails])
GO
The output shows the desired result in the below screenshot.
We can also use the WHERE clause along with the GROUP BY statement with this AVG. I have given another example where I have grouped all products and their total count based on the same condition specified in the WHERE clause which is the number of all products sold below the average price.
SELECT ProductName,
COUNT(ProductName) AS [Product count]
FROM [dbo].[OrderDetails]
WHERE Price < (SELECT AVG(Price) FROM [dbo].[OrderDetails])
GROUP BY ProductName
GO
You can see the number of each product that has been sold below the average price of all products in the below output.
SQL AVG with HAVING clause
Now, I will show the use of AVG with the HAVING clause. First, I have executed the below T-SQL statements to list ProductName, Product count, Average price of each product, and their total sales whose price is less than the average price of all products. Here I have used SQL COUNT, AVG, and SUM with WHERE and GROUP BY statements. I have used the AVG function in the WHERE clause as I have used in the above example.
SELECT ProductName,
COUNT(ProductName) AS [Product count],
AVG(Price) AS [Avg Price],
SUM(Price) AS [Total Sales]
FROM [dbo].[OrderDetails]
WHERE Price < (SELECT AVG(Price) FROM [dbo].[OrderDetails])
GROUP BY ProductName
GO
We can see the output of the above query in the below screenshot.
From the above results which show a list of all products whose price is less than the average price of all products, now, we will use the HAVING clause to apply one more filter to list out only those products whose average price is greater than $400 and their average price is less than the average price of all products.
Have a look at the below statement where I have added the HAVING clause. This condition will display the products whose average price is greater than $400 from the above result.
SELECT ProductName,
COUNT(ProductName) AS [Product count],
AVG(Price) AS [Avg Price],
SUM(Price) AS [Total Sales]
FROM [dbo].[OrderDetails]
WHERE Price < (SELECT AVG(Price) FROM [dbo].[OrderDetails])
GROUP BY ProductName
HAVING AVG(Price) > 400
GO
We can see the output shows only 2 products.
Now, I have removed the WHERE clause to show all products whose average price is more than $400. We can see the results below.
SQL AVG with NULL values
Here, I will show the use case of AVG with a NULL value. As mentioned, AVG skips NULL values defined in the expression and calculates the average value based on the remaining values from the column. I have updated a few records using the below statements.
Now, we will calculate the average price of all products from the table OrderDetails by running the below statement. This result is now different from the first query that we ran above. I have also executed one more statement to show the average of Ankit’s sales which now returns NULL.
Next Steps
- We learned how to use the SQL AVG function. Go ahead and start practicing these use cases to familiarize yourself with this aggregate functions.
- Check out – SQL Server SELECT Examples
- Check out – SSQL Aggregate Functions in SQL Server, Oracle and PostgreSQL
- Check out – SQL Server Window Aggregate Functions SUM, MIN, MAX and AVG
- Check out – SQL Server Stored Procedure Tutorial
