Written by 15:08 Database development, Statements

SQL AVG Function: A Complete Guide

CodingSight - SQL AVG Function

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
Average weight of a group of chimpanzees

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.

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
SQL AVG() with All

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.

Identical value for the unit price

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 Server eliminates the duplicate values 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() behavior with NULL values

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
SQL AVG() with GROUP BY and ORDER BY

We can also add the HAVING clause to filter results. For example, the following query filters record for [SalesOrderID] between 70550 and 72550.

Adding the HAVING clause to filter results

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:

SQL AVG() function and subqueries

The average price returned by the query is 328.4511.

SQL AVG() function and subqueries

Conclusion

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.

(Visited 66 times, 4 visits today)

Subscribe to our digest
to get SQL Server industry insides!

Close