PostgreSQL AVG Function

Summary: in this tutorial, you will learn how to use PostgreSQL AVG function to calculate average value of a set.

Introduction to PostgreSQL AVG function

The AVG function is one of the most commonly used aggregate function in PostgreSQL. It allows you to calculate average value of a numeric column. The syntax of the AVG function is as follows:

You can use the AVG function in the SELECT and HAVING clauses. Let’s take a look at some examples of using the AVG function.

We have the  payment table in the dvdrental sample database as follows:

PostgreSQL AVG function examples

If you want to know average amount that customers paid, you can apply the AVG function on the  amount column as the following query:

Note that we used to_char() function to convert the result into a formatted string.

PostgreSQL AVG function with DISTINCT

To calculate average value of a set with the condition that only distinct values are taken into calculation, you use DISTINCT as follows:

For example, the following query returns the average payment made by customers. Because we use DISTINCT, PostgreSQL only takes unique amounts and calculates the average.

Notice that the result is different from the first example.

PostgreSQL AVG function with SUM function

The following query uses both SUM function and AVG function to calculate the total payment made by customers and average of all transactions.

PostgreSQL AVG function with GROUP BY clause

To calculate the average value of a group, you use the AVG function with GROUP BY clause. First, the GROUP BY clause divides rows of the table into groups, the AVG function is then applied for each group.

For example, to calculate average amount paid by each customer, you use the following query:

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.

The following diagram illustrates how PostgreSQL performs the query.

PostgreSQL AVG function with HAVING clause

You can use the AVG function in the HAVING clause to filter the group based on a certain condition. For example, for all customers, you can get the customers who paid the average payment bigger than 5 USD. The following query helps you to do so:

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 average amount less than or equal to 5.

PostgreSQL AVG function and NULL values

How the AVG function treats null values when it calculates the average? Let’s test it.

First, we create a table named t1.

Second, we insert some sample data:

The data of the t1 table is as follows:

Third, we use the AVG function to calculate average values in the amount column.

We got 20. It means that the AVG function ignore NULL values when it calculates the average.

In this tutorial, we have shown you various examples using the PostgreSQL AVG function to calculate average value of a set.