PostgreSQL Average

In this PostgreSQL tutorial, I will show you how to use the PostgreSQL Average Count to compute the average value of the given set of values or columns.

You will learn how to use the AVG() function with the HAVING, GROUP BY clause, and the DISTINCT operator. Additionally, you will understand how to deal with null values while computing the average using the AVG() function.

PostgreSQL Average

The AVG() of PostgreSQL is an aggregate function that returns the average value of the given set. This set can be a collection of values or numeric columns. If you don’t know what the average is, then let me define it for you.

The average is a single value obtained by adding the numbers in a list and dividing the sum by the count, or the number of values or elements, in that list. In other words, if you have a list of numbers like (1,2,3) then find the average of these numbers.

You will first add all the numbers (1 + 2 + 3), which equals 6. Then, divide this sum by the number of elements in the list, such as a list containing three elements or numbers. For example, if the list contains three elements, divide 6 by 3, and you get two as the average.

Sometimes, when performing data analysis on the database, you may need to find the average value, and in such cases, the AVG() function is invaluable.

Approach-1 Using Basic AVG() function

The syntax of the AVG() function is given below.

AVG(list_of_values| column_name)

• AVG(): The aggregate function to compute the average value.
• list_of_values | column_name: It is the list or column name that contains the values whose average you want to find.

Let’s take a simple example of the AVG() function:

Suppose you have a table ‘orders’ with columns first_name, orders_date, and total_items, which is shown below.

If you look at the column ‘total_items’, which contains the number of items ordered by each customer. Now you want to find the average of that column, you can use the following query.

SELECT AVG(total_items) FROM orders;

After running the above query, you obtain the average value as 3.0, which represents the average number of items ordered by customers.

Let’s understand how the AVG() function works with different clauses, such as HAVING and GROUP BY.

Approach-2 Using Avg() with Null Value

What will happen if you set or columns contain null values? You don’t need to worry; the AVG() function can also handle columns or sets containing null values.

Look at the records in the ‘orders’ table where the total number of items ordered by customer ‘Jasleen’ is null.

Let’s run the same query that you executed in the examples above.

SELECT AVG(total_items) FROM orders;

Again, you found the same result. Although the ‘total_items’ column contains null values, the AVG() function computes the average value of the column by skipping these null values.

This is how the PostgreSQL AVG function handles columns or sets containing null values.

Approach-3 Using the HAVING clause

In general, you can’t use the WHERE clause to filter the result when you use the aggregate functions in your query; instead, use the HAVING clause to filter the group-based result based on the given condition.

Here, you will learn how to use the AVG() function with the HAVING clause.

Let’s say you want to find the average of total items ordered by customers with more than two items, and group and order by customer_id. So use the below query.

SELECT customer_id, AVG(total_items) FROM orders
GROUP BY customer_id
HAVING AVG(total_items) > 2
ORDER BY customer_id;

The above query returns the average value of the total_items column based on the customer_id, where the average value is greater than 2, as specified in the HAVING clause using AVG(total_items) > 2.

This is how you can use the AVG() function with the HAVING clause to find the average value of a set or column.

Approach-3 Using the Group By Clause

When you need to find the average of a set of columns in a table based on the group, you can use the AVG() function with the GROUP BY clause.

In this case, the GROUP BY clause creates a separate group based on the specified column, and then the AVG() function calculates the average value for each group separately.

Here, you will use the same example as in the previous section. Essentially, you will calculate the average of the total items grouped by customer_id. So use the below query.

SELECT customer_id, AVG(total_items) FROM orders
GROUP BY customer_id
ORDER BY customer_id;

If you examine the result of the above query, it returns the average value of total items based on the customer_id (1, 2, 3). It first groups the total items by customer_id, then computes the average of each group using the AVG() function.

As a result, the customers‘ IDs 1, 2, and 3 have average values of 2.5, 3.0, and 4.0, respectively.

Approach-4 With Distinct Values

Sometimes columns or sets contain duplicate values, and while calculating the average, you don’t want to include these duplicate values in the calculation of the average value. So, use the DISTINCT operator within the AVG() function before the column name.

The DISTINCT operator returns the unique values from a column, ignoring duplicate values.

For example, examine the ‘orders’ table, which contains duplicate values in the ‘total_items’ column for the customer ID equal to 3.

Now use the below query without the DISTINCT operator to compute the average of the column ‘total_itmes’.

SELECT AVG(total_items) FROM orders;

The query returns an average value of 3.166, considering the duplicate values.

Now run the same query with the DISTINCT operator.

SELECT AVG(DISTINCT total_items) FROM orders;

This time, you have used the DISTINCT operator within the AVG() function and got the result 3.0. The DISTINCT operator removes duplicate values and returns only the unique values from the columns.

This is how you can use the DISTINCT operator with AVG() function.

Conclusion

In this PostgreSQL tutorial, you have learned how to use the AVG() function to get the average value of the columns. Then learned about using the AVG() function with GROUP BY and HAVING clauses. At last, you covered how to use the DISTINCT operator within AVG() function and obtain the average value of the unique values in the column.

You may also read:

• How to use Postgres Min() Function
• PostgreSQL group by with examples
• Postgresql Having Clause + Examples