PostgreSQL - AVG() Function
Last Updated :
06 Aug, 2024
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.
Similar Reads
PostgreSQL - SUM() Function The SUM() function in PostgreSQL is used to calculate the sum of values in a numeric column. This article will guide you through the syntax, important considerations, and practical examples of using the SUM() function in PostgreSQL.SyntaxSUM(column) The following points need to be kept in mind while
2 min read
PostgreSQL - LAG Function In PostgreSQL, the LAG() function is a powerful window function that allows you to access data from a previous row within the same result set. It’s particularly useful for comparing values in the current row with values in the preceding row, making it ideal for analytical queries in PostgreSQL.For e
5 min read
PostgreSQL - LEAD Function In PostgreSQL, the LEAD() function is a powerful window function used to access a row that follows the current row at a specific physical offset. This function is generally employed to compare the value of the current row with the value of the next row following the current row.Let us better underst
3 min read
PostgreSQL - LAST_VALUE Function The PostgreSQL LAST_VALUE() function is a powerful window function used to retrieve the last value within a specified window frame of a query result set. It is particularly beneficial for performing advanced data analysis and retrieving the final value in ordered partitions.In this article, we’ll ex
4 min read
PostgreSQL - Function Parameters In PostgreSQL, functions provide an efficient way to encapsulate logic, perform calculations, and handle complex tasks within a database. A thorough understanding of PostgreSQL function parameters is essential for writing flexible and optimized functions.In this article, we will analyze different ty
5 min read
Compute Moving Average in PostgreSQL PostgreSQL is an advanced relational database system that supports both relational (SQL) and non-relational (JSON) queries. It is free and open-source. The moving average helps to level the price data over a specified period by creating a constantly updated average price. PostgreSQL which, is an imp
6 min read
Postgre Window Functions PostgreSQL is an advanced relational database management system, popular for its ability to handle both SQL (structured) and JSON (non-relational) queries. One of its most powerful features is window functions, which allow for complex data analysis across rows without collapsing data into a single r
6 min read
PostgreSQL - ALL Operator The PostgreSQL ALL operator is a powerful tool for comparing a value with a list of values returned by a subquery. This operator is essential for filtering and querying data based on comparisons with multiple values, making it a valuable addition to any PostgreSQL user's toolkit.Let us better unders
3 min read
PostgreSQL - HAVING clause The HAVING clause in PostgreSQL is an essential feature for filtering grouped data that has been aggregated using functions like SUM(), COUNT(), AVG(), and others. Unlike the WHERE clause, which filters rows before aggregation, the HAVING clause is used to filter results after the grouping and aggre
4 min read
PostgreSQL - GROUP BY clause The GROUP BY clause in PostgreSQL is an essential tool that allows us to group rows that share the same values in one or more columns. This powerful functionality is commonly used to perform aggregate calculations such as SUM(), COUNT(), AVG(), and more, enabling us to summarize data efficiently. In
4 min read