How to use AVG Function in SQL Server

by

In this SQL Server tutorial, you will understand how to use the AVG function in SQL Server to find the average value of the column data.

Here, you will learn about the syntax of the AVG() function and then understand ‘What does average value mean?’. After that, you will use the AVG() function on table columns data, then with the DISTINCT and HAVING clause.

AVG Function in SQL Server

The AVG function in SQL Server computes the average value of the specified column values. The average is the value that is the sum of all the numbers divided by how many numbers exist in your list.

AVG function in SQL Server is commonly used in statistical analysis or data science, etc.

For example, if you have a list of numbers [2,6,3,7], and to find the average value to these numbers, sum all the numbers in the list, as a result, 18, and divide the 18 by four because there are four numbers in the list. As a result, you get an average value equal to 4.5.

Similarly, if you have columns in your table that contain numeric values, then you can use the AVG() function on that column to calculate the average value.

The syntax is given below.

AVG( ALL | DISTINCT expression)

Where,

  • AVG(): It is the function for computing the average value in a given group.
  • ALL | DISTINCT: These are options that you can use with the AVG function in SQL Server to compute the average of all or distinct values of the columns.
    • If you use the AVG(ALL expression) like this, it applies the AVG() function to all the values of the specified columns and returns the average value.
    • In the case of AVG(DISTINCT expression), the AVG() function is applied to the unique values of the columns and computes the average value.
  • expression: It is the column values or group of the numeric value and any category containing the numeric data on which you can apply the AVG() function. But you can’t use the AVG() function on the column containing the data of bit data type.
  • If you pass the expression or the column value of type tinyint, smallint, and int, then the return type of the average value is of type int.
  • If the expression is of type bigint, decimal category(p,s), money or smallmoney, float, and real, then the return type of the function will be bigint, decimal(38, max(s,6)), money and float respectively.
  • When you use the AVG function in SQL Server without the OVER and ORDER BY clause, then it acts as a deterministic function. If you use the OVER and ORDER BY clause, then in this case, it acts as a non-deterministic function.

If the function is non-deterministic, then each call of this kind of function yields a different output, but in the case of the deterministic function, the function always returns the same value.

Let’s take a simple example where you will apply the AVG function in SQL Server on the table columns values to calculate the average value for that column.

Suppose a Sales table with columns SaleID, ProductID, and SaleAmount, shown below.

AVG Function in SQL Server Sales Table

Think that you work at an online retail store with a database with a Sales table, which you can see in the above picture. Now, you must compute the average sales amount per product using the query below.

SELECT ProductID, AVG(SaleAmount) AS AvgSalesPrice
FROM Sales
GROUP BY ProductID;
AVG Function in SQL Server

When you execute the above query, it groups all the products with the same product ID into a single group using the GROUP BY ProductID clause, then applies the AVG() function on each group separately on the SaleAmount column to compute the average sales amount for that group.

After that, you can see in the above output that it returns the two columns ProductID, which represents the unique product ID, and AvgSalesPrice, which contains the average sales amount for the corresponding product ID.

This is how you can apply the AVG function in SQL Server on column values to calculate the mean of all the values in that column.

AVG Function in SQL Server with DISTINCT Option

You can specify the DISTINCT keyword within the AVG() function to compute the average value of the unique values in a column. The DISTINCT keyword is used when you don’t want to include duplicate values while computing the average.

For example, look at the Sales table, which is shown below.

AVG Function in SQL Server with DISTINCT Option Sales Table

As you can see, the SaleAmount column contains duplicate values (sale amount for the product). So, while computing the average sales amount, you don’t want the duplicate values, you only want the unique ones. For that, use the below query.

SELECT ProductID, AVG(DISTINCT SaleAmount) AS AvgDistinctSaleAmount
FROM Sales
GROUP BY ProductID
AVG Function in SQL Server with DISTINCT Option

After executing the above query, it returns the average sale amount of the SaleAmount column values without including the duplicate values of that column.

Here is the query part AVG(DISTINCT SaleAmount). For each group, it identifies the unique value in the SaleAmount column using the DISTINCT keyword. Then, it computes the average of these unique values.

  • But don’t confuse it with the sale amount, which is 200.00 for ProductIDs 102 and 104, because here is the same sale amount for the different products with ID. So here, AVG(DISTINCT SaleAmount) is for the same product ID and sale amount.

This is how to compute the average value of the unique values of the column using the AVG function in SQL Serve with option DISTINCT.

AVG Function in SQL Server with HAVING clause

If you want to perform a more advanced filter, you can use the HAVING clause with the AVG function in SQL Server. As you know, the HAVING clause filters the data after grouping it using the GROUP BY clause.

The syntax for using the AVG() function with HAVING clause.

SELECT column_name1, AVG(column_name2) AS average_column_name2
FROM table_name
GROUP BY column_name1
HAVING AVG(column_name2) condition;

Where column_name1 and column_name2 are the columns of the table_name, the condition is the criteria with HAVING AVG(column_name2) that the average must meet.

For example, let’s use the same Sales table you used in the above sections. Now you have group sales by ProductID, compute the average sale amount for each product, and then filter out the products with an average sale amount > 200 value.

For that, use the below query.

SELECT ProductID, AVG(SaleAmount) AS AverageSaleAmount
FROM Sales
GROUP BY ProductID
HAVING AVG(SaleAmount) > 200;
AVG Function in SQL Server with HAVING clause

When you execute the above query, it first groups the rows in a Sale table by the ProductID using the GROUP BY ProductID clause. Then, it computes the average sale amount for each created group using the AVG(SaleAmount).

After that, it uses the HAVING clause to filter products where the average sale amount is greater than 200 dollars using the AVG(SaleAmount) > 200.

As a result, you can see that there are only two product IDs, 100 and 101, with sales greater than 200, 261.88 and 300.00, respectively.

This is how to use the AVG function in SQL Server with HAVING clause.

Conclusion

In this SQL Server tutorial, you have covered how to compute the average value of the column’s values using the AVG function in SQL Server. Then, you learned how to calculate the average value of the unique values in a table column. Finally, it covered the AVG() function with the HAVING clause.

You may like to read: