PostgreSQL Average Count

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 HAVING, GROUP BY clause, and DISTINCT operator. Additionally, you will understand how to deal with null values while computing the average using the AVG() function.

PostgreSQL Average Count

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

The average is a single value that is obtained by adding the list of numbers and dividing those numbers by the count or number of values or elements that exist 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 is equal to 6 and divide this number by a number of elements in the list such as a list containing 3 elements or numbers, so divide 6 by 3, and you get 2 as the average number.

So sometimes while performing some data analysis on the database, you might need to find the average value and at that time AVG() function will be very helpful.

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 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 columns, you can use the below query.

SELECT AVG(total_items) FROM orders;

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

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

PostgreSQL Average Count Null Value

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

Look at the records of the table ‘orders’ where the order of the total items by the customer ‘Jasleen’ is NULL.

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

SELECT AVG(total_items) FROM orders;

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

This is how the PostgreSQL AVERAGE function deals with columns or sets containing null values.

Using the HAVING clause with PostgreSQL Average Count

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 on the basis of the given condition.

So here you will see 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 greater than 2 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 column total_items based on the customer_id where the average value is greater than 2 which is 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 the set or column.

PostgreSQL Average Count Group By clause

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

So in this case, the GROUP BY creates a separate group based on the specified column in the GROUP BY clause, and then the AVG() function acts on these groups to compute the average value of each group separately.

Here you will use the same example that you have used in the above section, basically, 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 look at the result of the above query, it returned the average value of total items based on the customer_id (1, 2, 3). It first groups the total items based on the customer_id, then computes the average of each group by applying the AVG() function.

As a result the customers_id 1, 2, and 3 with an average value equal to 2.5, 3.0, and 4.0 respectively.

PostgreSQL Average Count with Distinct Values

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

The DISTINCT operator returns the unique values from the column and ignores the duplicate values.

For example, look at the table ‘orders’ which contains the duplicate values in the column ‘total_items’ 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 the average value of 3.166 by 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 AVG() function and got the result 3.0, the DISTINCT operator removes the duplicate values and returns only the unique value 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