In this article, we will learn the SQL Average function which is known as AVG() function in T-SQL. AVG() function is an aggregate function that calculates the average value of a numerical dataset that returns from the SELECT statement.
Introduction
Assume that we have a collection of numbers. Firstly, we add up all members of the collection and then divide the total member number of the collection. As a result, the obtained number will be the average. Let’s explain this mathematical notion with a straightforward example.
John is a student at university and decides to record his expenses every day. The following chart and table represent John’s last week’s expenses.
Now, we will calculate the last week’s average expense of John. At first, we will add up all the expenses for the aforementioned week.
($20+$60+$20+$42+$10+$15+$8) = $175 is the total amount of the expenses for the week.
In the second step, we will divide the total expense amount to 7 because this collection is formed on 7 members. In other words, a week consists of seven days.
$175 / 7 = $25 is the average expense of the week.
After discussing the mathematical concept of the average, let’s continue to learn the basics of the AVG() function in SQL.
Data Preparation
With the help of the following query, we will create the WeekExpense table and then insert John’s 3 weeks expenses. We will use this table in all examples of this article.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
CREATE TABLE WeekExpense ( WeekNumber VARCHAR(20),WeekDayName VARCHAR(50), Expense MONEY) INSERT INTO WeekExpense VALUES ('Week05','Monday', 20 ), ( 'Week05','Tuesday', 60 ), ( 'Week05','Wednesday', 20 ), ( 'Week05','Thurusday', 42 ), ( 'Week05','Friday', 10 ), ( 'Week05','Saturday', 15 ) , ( 'Week05','Sunday', 8 ), ('Week04','Monday', 29 ), ( 'Week04','Tuesday', 17 ), ( 'Week04','Wednesday', 42 ), ( 'Week04','Thurusday', 11 ), ( 'Week04','Friday', 43 ), ( 'Week04','Saturday', 10 ) , ( 'Week04','Sunday', 15 ), ('Week03','Monday', 10 ), ( 'Week03','Tuesday', 32 ), ( 'Week03','Wednesday', 35 ), ( 'Week03','Thurusday', 19 ), ( 'Week03','Friday', 30 ), ( 'Week03','Saturday', 10 ) , ( 'Week03','Sunday', 15 ) GO SELECT * FROM WeekExpense |
SQL Average function syntax
AVG() syntax function will look like the following in its simple form:
|
1 2 3 |
SELECT AVG ( [ ALL | DISTINCT ] columname ) FROM TABLENAME WHERE CONDITION |
ALL keyword enables us to calculate an average for all values of the resultset and it is used by default. The DISTINCT keyword implements the AVG() function only for unique values.
AVG() function example
The following query will calculate John’s average expense with the help of the AVG() function.
|
1 2 3 |
SELECT AVG(Expense) AS [Avg_Expense] FROM WeekExpense WHERE WeekNumber = 'Week05' |
The following image illustrates the calculation methodology of the AVG() function by default usage.
As we can see, AVG() considers all weekdays and weekends values in its calculation. Also, we can obtain the same result when we add the ALL keyword to syntax.
|
1 2 3 |
SELECT AVG(ALL Expense) AS [Avg_Expense] FROM WeekExpense WHERE WeekNumber ='Week05' |
If we want to ignore duplicate values during the AVG() function calculation, we can use the DISTINCT keyword. After executing the query below, let’s analyze the result:
|
1 2 3 |
SELECT AVG(DISTINCT Expense) AS [Avg_Expense] FROM WeekExpense WHERE WeekNumber ='Week05' |
DISTINCT keyword eliminates duplicate values, therefore, it takes into account only one of the expenses whose values are $20 in the calculation. The following image basically illustrates the working mechanism of the DISTINCT keyword.
SQL Average function and NULL values
AVG() function does not consider the NULL values during its calculation. Now, we will study an example of this issue. At first, we will update Sunday expenses as NULL in the WeekExpense table.
|
1 2 3 4 5 |
UPDATE WeekExpense SET Expense = NULL WHERE WeekDayName = 'Sunday' AND WeekNumber = 'Week05' |
Now, we will execute the following query in order to calculate the average value.
|
1 2 3 |
SELECT AVG(Expense) AS [Avg_Expense] FROM WeekExpense WHERE WeekNumber ='Week05' |
As we can see, the NULL value did not take into account by the AVG() function in the calculation. The following image illustrates the calculation method:
If we want to include the NULL values into the calculation, we can use the ISNULL function. ISNULL function is used to change the NULL values into the defined values. So we will execute the following in order to include NULL expressions into the calculation.
|
1 2 3 |
SELECT AVG(ISNULL(Expense,0)) AS [Avg_Expense] FROM WeekExpense WHERE WeekNumber ='Week05' |
The following image illustrates the calculation method of the previous query:
At this point, we should remark here is that we included the NULL expression to calculation as 0.
SQL Average function usage with GROUP BY statement
GROUP BY statement is used for grouping the data and it mainly uses with aggregate functions.
John decided to calculate the average expense of all weeks. To handle John’s issue, we need to use GROUP BY statement and AVG() function at the same time. The following query will calculate the average expense by each individual week.
|
1 2 3 4 |
SELECT WeekNumber, AVG(Expense) AS [Avg_Expense] FROM WeekExpense GROUP BY WeekNumber ORDER BY WeekNumber DESC |
When we have taken a glance at the result set of the query, we can see that the averages were calculated for all weeks separately.
Bonus Tip: Execution plan details of the SQL Average function
The execution plan helps to understand the execution details of a query. When we analyze the execution plan of a query, we can obviously understand what’s happening behind the scenes.
Now, we will analyze the following query actual execution plan with ApexSQL Plan so that we can clearly understand what happens behind the scenes of the AVG() function.
|
1 2 |
SELECT AVG(Expense) AS [Avg_Expense] FROM WeekExpense |
The visual execution plan of the query is shown as below:
The Table scan read all rows in the tables because we don’t create any index in this table. In the next step, the Stream aggregate operator computes the sum and the count value of the expressions.
Compute scalar takes these expressions from Stream aggregate and calculates the average value through the following formula.
|
1 2 3 4 5 |
CASE WHEN [Expr1004] = (0) THEN NULL ELSE [Expr1005] / CONVERT_IMPLICIT(money, [Expr1004], 0) END |
In this formula, we should remark on one point. When the total number of the expressions will return 0, the average calculation will return NULL. If the total number of expressions returns 0, the average value will be NULL. The main intention of this is to avoid divide by zero error.
Conclusion
In this article, we learned SQL Average function and reinforced our learning with basic examples and illustrations. Along the way, we discussed the execution plan details of the AVG() function.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023