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:

AVG(column)Code language: SQL (Structured Query Language) (sql)

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:

AVG(DISTINCT column)Code language: SQL (Structured Query Language) (sql)

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:

payment table

1) Basic PostgreSQL AVG() function example

The following example uses the AVG() function to calculate the average amount that customers paid:

SELECT AVG(amount)
FROM payment;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

        avg
--------------------
 4.2006056453822965
(1 row)Code language: Shell Session (shell)

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

SELECT AVG(amount)::numeric(10,2) 
FROM payment;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 avg
------
 4.20
(1 row)Code language: Shell Session (shell)

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.

SELECT AVG(DISTINCT amount)::numeric(10,2)
FROM payment;Code language: SQL (Structured Query Language) (sql)

Output:

 avg
------
 6.14
(1 row)Code language: CSS (css)

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.

SELECT
	AVG(amount)::numeric(10,2),
	SUM(amount)::numeric(10,2)
FROM
	payment;Code language: SQL (Structured Query Language) (sql)
 avg  |   sum
------+----------
 4.20 | 61312.04
(1 row)

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:

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;Code language: SQL (Structured Query Language) (sql)

Output:

 customer_id | first_name  |  last_name   | avg
-------------+-------------+--------------+------
           1 | Mary        | Smith        | 3.82
           2 | Patricia    | Johnson      | 4.76
           3 | Linda       | Williams     | 5.45
           4 | Barbara     | Jones        | 3.72
...

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:

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;Code language: SQL (Structured Query Language) (sql)

Output:

 customer_id | first_name | last_name | avg
-------------+------------+-----------+------
           3 | Linda      | Williams  | 5.45
          19 | Ruth       | Martinez  | 5.49
         137 | Rhonda     | Kennedy   | 5.04
         181 | Ana        | Bradley   | 5.08
         187 | Brittany   | Riley     | 5.62
         209 | Tonya      | Chapman   | 5.09
         259 | Lena       | Jensen    | 5.16
         272 | Kay        | Caldwell  | 5.07
         285 | Miriam     | Mckinney  | 5.12
         293 | Mae        | Fletcher  | 5.13
         310 | Daniel     | Cabral    | 5.30
         311 | Paul       | Trout     | 5.39
         321 | Kevin      | Schuler   | 5.52
         470 | Gordon     | Allard    | 5.09
         472 | Greg       | Robins    | 5.07
         477 | Dan        | Paine     | 5.09
         508 | Milton     | Howland   | 5.29
         522 | Arnold     | Havens    | 5.05
         542 | Lonnie     | Tirado    | 5.30
         583 | Marshall   | Thorn     | 5.12
(20 rows)

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.

CREATE TABLE t1 (
  id serial PRIMARY KEY, 
  amount INTEGER
);
Code language: SQL (Structured Query Language) (sql)

Second, insert some sample data:

INSERT INTO t1 (amount) 
VALUES 
  (10), 
  (NULL), 
  (30);Code language: SQL (Structured Query Language) (sql)

The data of the t1 table is as follows:

SELECT 
  * 
FROM 
  t1;Code language: SQL (Structured Query Language) (sql)

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

SELECT AVG(amount)::numeric(10,2) 
FROM t1;Code language: SQL (Structured Query Language) (sql)

Output:

  avg
-------
 20.00
(1 row)Code language: CSS (css)

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.
Was this tutorial helpful ?