In SQL Server, the AVG() function is an aggregate function that calculates the average value of a numerical dataset returned from a SELECT statement. This function is useful when you need to find the average of a set of values, such as calculating the average expense or average sales.

Introduction

Let’s start by understanding the mathematical concept of average. Assume we have a collection of numbers. To calculate the average, we add up all the numbers in the collection and then divide the total by the number of members in the collection. For example, if John recorded his expenses for a week as $20, $60, $20, $42, $10, $15, and $8, the total expense for the week would be $175. Dividing this total by 7 (the number of days in a week) gives us an average expense of $25.

Using the AVG() Function

In SQL Server, we can use the AVG() function to calculate the average of a set of values. The syntax of the AVG() function is as follows:

SELECT AVG([ALL | DISTINCT] columnname) FROM tablename WHERE condition

The ALL keyword calculates the average for all values in the result set, which is the default behavior. The DISTINCT keyword calculates the average only for unique values.

Let’s consider an example where we have a table called “WeekExpense” that stores John’s expenses for different weeks. We can calculate John’s average expense for a specific week using the AVG() function:

SELECT AVG(Expense) AS Avg_Expense FROM WeekExpense WHERE WeekNumber = 'Week05'

This query will return the average expense for Week 05. If we want to include duplicate values in the calculation, we can use the DISTINCT keyword:

SELECT AVG(DISTINCT Expense) AS Avg_Expense FROM WeekExpense WHERE WeekNumber = 'Week05'

The AVG() function does not consider NULL values during its calculation. If we want to include NULL values, we can use the ISNULL function to replace NULL with a defined value:

SELECT AVG(ISNULL(Expense, 0)) AS Avg_Expense FROM WeekExpense WHERE WeekNumber = 'Week05'

Using the AVG() Function with GROUP BY

The GROUP BY statement is used to group data and is often used with aggregate functions like AVG(). For example, if we want to calculate the average expense for each individual week, we can use the GROUP BY statement:

SELECT WeekNumber, AVG(Expense) AS Avg_Expense FROM WeekExpense GROUP BY WeekNumber ORDER BY WeekNumber DESC

This query will return the average expense for each week, grouped by the WeekNumber column.

Execution Plan Details

The execution plan helps us understand the execution details of a query. When we analyze the execution plan of a query, we can see what happens behind the scenes. For example, when we analyze the execution plan of a query that uses the AVG() function, we can see that a table scan reads all the rows in the table, and a stream aggregate operator computes the sum and count values of the expressions.

By understanding the execution plan, we can optimize our queries and improve performance.

Conclusion

In this article, we have learned about the SQL Average function (AVG()) and how to use it to calculate the average value of a dataset. We have also explored different scenarios, such as handling NULL values and using the AVG() function with the GROUP BY statement. Understanding the AVG() function and its usage can help us perform calculations and analyze data effectively in SQL Server.