# SQL AVG Function

Summary: in this tutorial, you will learn how to use the SQL AVG aggregate function to calculate the average of a set of numbers.

## Introduction to SQL AVG function

The `AVG` function calculates the average of the values. To use the `AVG` function, you use the following syntax:

You can specify `ALL` or `DISTINCT` modifier before the expression.

• `ALL` modifier means that the `AVG` function is applied to all values including duplicates. The  `AVG()` function uses the `ALL` modifier by default if you do not specify any modifier explicitly.
• `DISTINCT` modifier means that the `AVG` function is applied to only distinct values in the set of values.

Notice that the `AVG` function ignores `NULL` values.

The `AVG` function returns a single value whose data type is determined by the type of the result of the expression. The returned data type could be any numeric type such as integer, float, etc.

Behind the scenes, the `AVG` function calculates the average of values by dividing the total of these values by the number of values except for the `NULL` values. Therefore, if the total of those values exceeds the maximum value of data type of the result, the database server will issue an error.

The `AVG` function is one of an ANSI SQL aggregate functions, therefore, it is available in all relational database management systems e.g., Oracle, Microsoft SQL Server, MySQL, PostgreSQL, etc.

## SQL AVG function examples

We will use the `products` table in the sample database for the demonstration of the  `AVG()` function in the following sections. ### Simple SQL AVG function example

The following query calculates the average of unit prices of all products in the `products` table. The query calculates the total unit prices and divides the total by the number of rows in the `products` table.

To calculate the average of distinct unit prices of products, you can use the `DISTINCT` modifier in the  `AVG()` function as the following query:

### SQL AVG function with GROUP BY clause

To find the average of unit prices for each product’s category, you can use the `AVG` function with the GROUP BY clause as the following query: The INNER JOIN clause is used to get the category name from the `categories` table.

### SQL AVG function with HAVING clause

To get the category that has an average unit price greater than \$25, you use the AVG function with  `GROUP BY` and HAVING clauses as the following query: In this tutorial, we have shown you how to use the SQL `AVG` function to calculate the average of a list of values.