PostgreSQL Tutorial: AVG Function

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

Table of Contents

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.

The syntax of the AVG() function is as follows:

AVG(column)

You can use the AVG() function in the SELECT and HAVINGclauses.

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:

PostgreSQL AVG function examples

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

SELECT AVG(amount)
FROM payment;

        avg
--------------------
 4.2006056453822965
(1 row)

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

SELECT AVG(amount)::numeric(10,2) 
FROM payment;

 avg
------
 4.20
(1 row)

PostgreSQL AVG function with DISTINCT operator

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

AVG(DISTINCT column)

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

SELECT AVG(DISTINCT amount)::numeric(10,2)
FROM payment;

 avg
------
 6.14
(1 row)

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

PostgreSQL AVG function with SUM function

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

SELECT
	AVG(amount)::numeric(10,2),
	SUM(amount)::numeric(10,2)
FROM
	payment;

 avg  |   sum
------+----------
 4.20 | 61312.04
(1 row)

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 to each group.

The following example uses the AVG() function with GROUP BY clause to calculate the average amount paid by each customer:

SELECT
	customer_id,
	first_name,
	last_name,
	AVG (amount)::NUMERIC(10,2)
FROM
	payment
INNER JOIN customer USING(customer_id)
GROUP BY
	customer_id
ORDER BY
	customer_id;

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.

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:

SELECT
	customer_id,
	first_name,
	last_name,
	AVG (amount)::NUMERIC(10,2)
FROM
	payment
INNER JOIN customer USING(customer_id)
GROUP BY
	customer_id
HAVING
	AVG (amount) > 5
ORDER BY
	customer_id;

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.

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.

CREATE TABLE t1 (
	id serial PRIMARY KEY,
	amount INTEGER
);

Second, insert some sample data:

INSERT INTO t1 (amount)
VALUES
	(10),
	(NULL),
	(30);

The data of the t1 table is as follows:

SELECT	* FROM 	t1;

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

SELECT AVG(amount)::numeric(10,2) 
FROM t1;

  avg
-------
 20.00
(1 row)

It returns 20, meaning that the AVG() function ignores NULL values.

In this tutorial, you have learned how to use the PostgreSQL AVG() function to calculate the average value of a set.

See more

PostgreSQL Tutorial: Aggregate Functions

PostgreSQL Documentation: Aggregate Functions