Esat Erkec
AVG() function execution plan

SQL AVG() function introduction and examples

March 2, 2020 by

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.

Calculation of the average in math

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.

WeekExpense table resultset

SQL Average function syntax

AVG() syntax function will look like the following in its simple form:

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.

A simple example of the AVG() function

The following image illustrates the calculation methodology of the AVG() function by default usage.

Illustration of the SQL average function

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.

A simple example of the AVG() function with ALL keyword

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:

A simple example of the AVG() function with DISTINCT keyword

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.

Illustration of the SQL average function with 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.

Now, we will execute the following query in order to calculate the average value.

AVG() function example result

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:

Illustration of the SQL average function and NULL expressions interaction

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.

Usage of the AVG() function with ISNULL

The following image illustrates the calculation method of the previous query:

How to eliminate NULL values in SQL average function

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.

AVG() function usage with GROUP BY statment

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.

The visual execution plan of the query is shown as below:

AVG() function execution plan

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.

Stream aggregate operator

Compute scalar takes these expressions from Stream aggregate and calculates the average value through the following formula.

Compute scalar operator

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.

Esat Erkec
Latest posts by Esat Erkec (see all)
227 Views