**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:

1 | AVG(column) |

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:

1 2 3 4 5 6 7 | SELECT to_char( AVG (amount), '99999999999999999D99' ) AS average_amount FROM payment; |

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:

1 | AVG(DISTINCT column) |

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.

1 2 3 4 5 6 7 | SELECT TO_CHAR( AVG (DISTINCT amount), 'FM999999999.00' ) FROM payment; |

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.

1 2 3 4 5 6 7 8 9 10 11 | SELECT TO_CHAR( AVG (amount), 'FM999999999.00' ) AS "Average", TO_CHAR( SUM (amount), 'FM999999999.00' ) AS "Total" FROM payment; |

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SELECT customer.customer_id, first_name, last_name, to_char( AVG (amount), '99999999999999999D99' ) AS average_amount FROM payment INNER JOIN customer ON customer.customer_id = payment.customer_id GROUP BY customer.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.

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SELECT customer.customer_id, first_name, last_name, to_char( AVG (amount), '99999999999999999D99' ) AS average_amount FROM payment INNER JOIN customer ON customer.customer_id = payment.customer_id GROUP BY customer.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 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.

1 2 3 4 | CREATE TABLE t1 ( ID serial PRIMARY KEY, amount INTEGER ); |

Second, we insert some sample data:

1 2 3 4 5 | INSERT INTO t1 (amount) VALUES (10), (NULL), (30); |

The data of the t1 table is as follows:

1 2 3 4 | SELECT * FROM t1; |

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

1 2 3 4 | SELECT AVG (amount) FROM t1; |

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.