In mathematics, the Average value represents the middle value that we calculate by dividing the sum of all values by the number of values.
For example, the following figure from the popular mathsisfun resource presents a group of chimpanzees, and we have to calculate the average weight. To do it, we have to cover the following steps:
- Sum of a group of chimpanzees: 47+58+41+40 = 186 kg
- Count of chimpanzees = 4
- Average weight is 186/4= 46.5 kg
SQL Server also provides the average function – AVG() for calculating Average in T-SQL scripts. Let’s explore it in detail.
To illustrate the examples, I am using the sample data from the SalesLT schema in Azure SQL Database. You can refer to Microsoft docs to set it up from the Azure portal.
SQL Server AVG Function Example
The following SQL script calculates the average unit price for [SalesOrderID] stored in the [SalesOrderDetail] table.
SELECT Avg([UnitPrice]) AS AvgUnitPrice FROM [SalesLT].[SalesOrderDetail] WHERE SalesLT.SalesOrderDetail.SalesOrderID=71831
- The sum of the unit prices for all orders belonging to the sales order id 71831 is 1460.982
- The average unit price = 1460.982/3 = 486.994
The following figure displays the order quantity and the average unit price for the sales order id 71831.
SQL Server Average Function with All
The SQL AVG() function considers all data rows that satisfy predicates in the where clause. It means you can also use an optional keyword ALL. It returns a similar result:
SELECT Avg(ALL[UnitPrice]) AS AvgUnitPrice FROM [SalesLT].[SalesOrderDetail] WHERE SalesLT.SalesOrderDetail.SalesOrderID=71831
Suppose you have duplicate values in your database table. You do not want SQL to consider some duplicate value while calculating the average unit price. The above example for the sales order ID 71831 has an identical value for the unit price, as shown below.
SQL AVG Function with DISTINCT
In this case, we can use another keyword – DISTINCT – to let our SQL Server know that it needs to consider DISTINCT values for the Average.
SELECT * FROM [SalesLT].[SalesOrderDetail] WHERE SalesLT.SalesOrderDetail.SalesOrderID=71831 Go SELECT Avg(DISTINCT[UnitPrice]) AS AvgUnitPrice FROM [SalesLT].[SalesOrderDetail] WHERE SalesLT.SalesOrderDetail.SalesOrderID=71831
With the DISTINCT keyword, the calculation for SQL AVG() works as below:
- SUM Of distinct unit price: 41.994 + 1376.994 = 1418.988
- Average unit price = 1418.994/2 = 709.494
The following figure proves that SQL Server eliminates the duplicate values ( in this example, it is 41.994) when it calculates the Average from specified values.
SQL AVG Behavior and NULL Values Example
Suppose you have a NULL value available in a column for which you want to calculate the Average.
Does SQL Server consider that row for Average?
Let’s find the answer practically. I have updated the unit price for a specific row as NULL with the following script that creates a test table and inserts few records into it:
CREATE TABLE TestAvgTable (ID int null) GO INSERT INTO TestAvgTable(ID) values(1),(2),(3),(4),(5),(NULL) GO SELECT Avg(ID) as CalculatedAvg FROM TestAvgTable
The Average of all values in the ID column is 3. We do have NULL values in that column. Therefore, SQL Server does not consider NULL values and calculates the Average as below:
- Sum of values: 1+2+3+4+5 = 15
- Average = 15/5= 3
SQL AVG with GROUP BY and ORDER BY Statements
In the previous examples, we calculated the average unit price for a single sales order id. Suppose we must calculate it for all sales order ids available in the [SalesOrderDetail] table. How do we achieve it?
SQL Server provides the GROUP BY function to similar group rows for calculating aggregations.
In the following query, we specify GROUP BY on the [SalesOrderID] column. It will group values with similar [SalesOrderID] and calculate the Average on them.
The query also uses the ORDER BY clause to sort results in descending order based on the column [CountofOrderQty] values.
Thus, our query in the below example displays the top 5 records based on the sorted results in descending order – the top 5 records with the highest values in the [CountofOrderQty] column.
SELECT TOP 5 [SalesOrderID] ,COUNT(OrderQty) AS CountofOrderQty, AVG([UnitPrice]) AS AvgUnitPrice FROM [SalesLT].[SalesOrderDetail] GROUP BY SalesLT.SalesOrderDetail.SalesOrderID ORDER BY CountofOrderQty DESC
We can also add the HAVING clause to filter results. For example, the following query filters record for [SalesOrderID] between 70550 and 72550.
SQL AVG Function Usage with Subqueries
SQL AVG() function can be used multiple times in the SELECT SQL Statements and inside the subquery. Let’s take an example query that has to perform the following tasks:
- The inner SELECT statement calculates the average unit price after grouping column [SalesOrderID]. It has the avg price for each sales order id.
- The outer SELECT statement calculates the average unit price of the results obtained from the inner block. It has the avg price for all sales IDs.
SELECT AVG(avg_unit_price) AS AvgUnitPrice FROM ( SELECT SalesOrderID, AVG([UnitPrice]) avg_unit_price FROM [SalesLT].[SalesOrderDetail] GROUP BY SalesLT.SalesOrderDetail.SalesOrderID ) t;
The following figure illustrates the code functionality:
The average price returned by the query is 328.4511.
Thus, we have explored several essential use cases of the SQL AVG() function to calculate average values.
However, writing all such queries manually is not the best option now when the work processes tend to eliminate manual routines. In such cases, professional software solutions come in handy. One of them is Devart SQL Complete that includes a helpful feature called SSMS Grid Aggregates.
This feature allows the users to avoid writing queries and functions. Instead, you can select several cells in a grid and receive the AVG value as well as the MIN, MAX, and SUM values.