---
title: 'PostgreSQL AVG Function'
page_title: 'PostgreSQL AVG function'
page_description: 'In this tutorial, you will learn how to use PostgreSQL AVG function to calculate average value of a numeric column.'
prev_url: 'https://www.postgresqltutorial.com/postgresql-aggregate-functions/postgresql-avg-function/'
ogImage: '/postgresqltutorial/payment-table.png'
updatedOn: '2024-01-25T13:26:11+00:00'
enableTableOfContents: true
previousLink:
  title: 'PostgreSQL Aggregate Functions'
  slug: 'postgresql-aggregate-functions/'
nextLink:
  title: 'PostgreSQL COUNT Function'
  slug: 'postgresql-aggregate-functions/postgresql-count-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:

```sqlsql
AVG(column)
```

You can use the `AVG()` function in the [`SELECT`](../postgresql-tutorial/postgresql-select) and [`HAVING`](../postgresql-tutorial/postgresql-having) clauses.

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

```sql
AVG(DISTINCT column)
```

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](../postgresql-getting-started/postgresql-sample-database 'PostgreSQL Sample Database') for demonstration:

![payment table](/postgresqltutorial/payment-table.png)

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

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

```sql
SELECT AVG(amount)
FROM payment;
```

Output:

```shell
        avg
--------------------
 4.2006056453822965
(1 row)
```

To make the output more readable, you can use the [cast](../postgresql-tutorial/postgresql-cast) operator as follows:

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

Output:

```shell
 avg
------
 4.20
(1 row)
```

### 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.

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

Output:

```text
 avg
------
 6.14
(1 row)
```

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.

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

```text
 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;
```

Output:

```text
 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](../postgresql-tutorial/postgresql-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;
```

Output:

```text
 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](../postgresql-tutorial/postgresql-create-table) named `t1`.

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

```

Second, [insert](../postgresql-tutorial/postgresql-insert) some sample data:

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

The data of the `t1` table is as follows:

```sql
SELECT
  *
FROM
  t1;
```

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

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

Output:

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

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.