PostgreSQL – AVG() Function

In PostgreSQL, the AVG() function is a powerful tool used to calculate the average value of a set of numeric values. It is one of the most frequently used aggregate functions in PostgreSQL, making it a crucial part of any database user’s toolkit. This function allows users to efficiently compute the average of a numeric column, which is often needed for data analysis and reporting.

Let us get a better understanding of the AVG() Function in PostgreSQL from this article.

Syntax

AVG(column)

This function can be used with both the SELECT and HAVING clauses, providing flexibility in various querying scenarios.

PostgreSQL AVG() Function Examples

Let’s look into some examples to see how the AVG() function works in PostgreSQL. We will be using the payment table in the dvdrental sample database for demonstration. 

Example 1: Calculating the Average Payment Amount

In this example, we will query to know the average amount that customers paid, using the AVG() function in the amount column as follows.

Query:

SELECT
    to_char(
        AVG (amount),
        '99999999999999999D99'
    ) AS average_amount
FROM
    payment;

Output:

Explanation: The ‘to_char()’ function converts the result into a formatted string, making it easier to read.

Example 2: Calculating Average Payment Amount by Customer

We will be using the payment table in the sample database for demonstration. In this example we will query to know the average amount paid by each customer using the command below.

Query:

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;

Output: The query will produce a list of customers along with their average payment amounts, formatted as specified.

Explanation:

• ‘INNER JOIN customer ON customer.customer_id = payment.customer_id’: This joins the ‘payment’ and customer tables based on the ‘customer_id’.
• ‘GROUP BY customer.customer_id, first_name, last_name’: This groups the results by customer ID, ensuring each customer’s average payment is calculated separately.
• ‘ORDER BY customer_id’: This orders the results by customer ID for better readability.

Conclusion

The AVG() function in PostgreSQL is an essential tool for calculating average values in your datasets. By understanding its syntax and various applications, you can effectively analyze your data and derive meaningful insights. The AVG() function provides the flexibility and power you need for calculating overall averages or breaking them down by specific groups.