# PostgreSQL AVG Function

**Summary**: in this tutorial, you will learn how to use PostgreSQL `AVG()`

function to calculate the average value of a set.

## Introduction to PostgreSQL AVG() function

The `AVG()`

function is one of the most commonly used aggregate functions in PostgreSQL. The `AVG()`

function allows you to calculate the average value of a set.

Here is the syntax of the `AVG()`

function:

You can use the `AVG()`

function in the `SELECT`

and `HAVING`

clauses.

To calculate the average value of distinct values in a set, you use the distinct option as follows:

Notice that the `AVG()`

function ignores `NULL`

. If the column has no values, the `AVG()`

function returns `NULL`

.

## PostgreSQL AVG() function examples

Let’s take a look at some examples of using the `AVG`

function.

We will use the following `payment`

table in the dvdrental sample database for demonstration:

### 1) Basic PostgreSQL AVG() function example

The following example uses the `AVG()`

function to calculate the average amount that customers paid:

Output:

To make the output more readable, you can use the cast operator as follows:

Output:

### 2) Using AVG() function with DISTINCT operator example

The following query returns the average payment made by customers. Because we use `DISTINCT`

PostgreSQL takes unique amounts and calculates the average.

Output:

Notice that the result is different from the first example that does not use the `DISTINCT`

option.

### 3) Using AVG() function with SUM() function example

The following query uses the `AVG()`

function with the `SUM()`

function to calculate the total payment made by customers and the average of all transactions.

### 4) Using PostgreSQL AVG() function with GROUP BY clause

Typically, you use the AVG() function with the GROUP BY clause to calculate the average value of per group.

- First, the
`GROUP BY`

clause divides rows of the table into groups - Then, the
`AVG()`

function calculates the average value per group.

The following example uses the `AVG()`

function with `GROUP BY`

clause to calculate the average amount paid by each customer:

Output:

In the query, we joined the `payment`

table with the `customer`

table using inner join. We used `GROUP BY`

clause to group customers into groups and applied the `AVG()`

function to calculate the average per group.

### 5) PostgreSQL AVG() function with HAVING clause example

You can use the `AVG()`

function in the `HAVING`

clause to filter groups based on a specified condition.

The following example uses the `AVG()`

function to calculate the average payment of each customer and return only the ones who paid higher than 5 USD:

Output:

This query is similar to the one above with an additional `HAVING`

clause. We used `AVG`

function in the `HAVING`

clause to filter the groups that have an average amount less than or equal to 5.

### 6) Using PostgreSQL AVG() function and NULL

Let’s see the behavior of the `AVG()`

function when its input has NULL.

First, create a table named `t1`

.

Second, insert some sample data:

The data of the `t1`

table is as follows:

Third, use the `AVG()`

function to calculate average values in the amount column.

Output:

It returns 20, meaning that the `AVG()`

function ignores `NULL`

values.

## Summary

- Use PostgreSQL
`AVG()`

function to calculate the average value of a set. - The
`AVG()`

function ignores NULL in the calculation. - The
`AVG()`

function returns NULL if the set is empty.