SQL AVG Function: A Complete Guide

In mathematics, the Average value represents the middle value that we calculate by dividing the sum of all values by the number of values.

We use the AVG function in Excel all the time. 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 Average or AVG() is the SQL Server function for calculating the Average in T-SQL scripts. The current article will study this function and illustrate it with examples.

Note: 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

To calculate the Average function in SQL, we use a simple example. Let’s define the average unit price for [SalesOrderID] stored in the [SalesOrderDetail] table.

SELECT Avg([UnitPrice]) AS AvgUnitPrice
      FROM [SalesLT].[SalesOrderDetail]
      WHERE SalesLT.SalesOrderDetail.SalesOrderID=71831

In this example, we apply the SELECT Average SQL command. The results we get are the following:

• 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

The TSQL Average can use different keywords. Here, we are applying 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 a 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. We can use the SQL Average GROUP BY option. In the following example, we’ll 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 Average 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 average 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 functionality of the code of the Average function in SQL:

The average price returned by the query is 328.4511.

Conclusion

Thus, we have explored several essential use cases of SQL AVG() to calculate the average value of a function.

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.

Tags: sql average, sql functions, sql server Last modified: October 07, 2022