How to Use Postgresql Having Clause

In this PostgreSQL tutorial, I will show you how to use PostgreSQL Having clause where you will learn how to filter the aggregated result using the HAVING clause based on the given condition.

Additionally, I will show how to use different aggregate functions such as count(), sum(), min(), and max() along with the HAVING clause.

How to Use Postgresql Having Clause

HAVING clause in a PostgreSQL allows you to perform the group-based filtering on specified conditions, unlike the WHERE clause which performs filtering on the columns.

Always use the HAVING clause after the GROUP BY and before the ORDER BY clause.

The syntax is given below.

SELECT column_1, AGGRGATE(column_2) FROM table_name
WHERE condition
GROUP BY column_name
HAVING condition
ORDER BY

• SELECT column_1, AGGRGATE(column_2) FROM table_name: Getting column and performing aggregation on the column from a table.
• WHERE condition: Use this to filter the column or row based on the condition.
• GROUP BY column_name: Group the result according to the specific column of the table.
• HAVING condition: To filter the group returned by the GROUP BY clause based on the given condition.
• ORDER BY: To order the result set in a specific order such as ascending and descending order.

Let’s take a simple example, suppose you have a table ’employees’ with columns ‘id’, ‘name’, and ‘salary’ which is shown below.

Now you want the group of employees whose salary is greater than $60000 and group the employee according to their name and salary. So for that use the below query.

SELECT name, salary FROM employees
GROUP BY name, salary
HAVING salary > 60000;

In the above syntax, the (GROUP BY name, salary) groups the employees by name and then salary, (HAVING salary > 60000) filters the salary of the employees where the employee’s salary is greater than 60000 as you can see in the output.

Using Postgresql Having Clause with Count()

The COUNT() function of PostgreSQL returns all the non-null unique values from the column. You can use the COUNT() with the HAVING clause to filter the values from the group.

Let’s take an example, suppose you have table ‘orders’ with columns ‘order_id’, ‘customer_id’, ‘order_date’, and ‘total_item’, table is shown below.

Now, if you want to find which customer has ordered more than one item, you can use the COUNT() with the HAVING clause.

SELECT customer_id, COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;

In the above syntax, (GROUP BY customer_id) groups the rows by customer_id and the COUNT(order_id) function computes the number of orders by each customer and gives an alias as order_count. Then (HAVING COUNT(order_id) > 1) clause filters the customers with more than one order.

How to use Postgresql Having Clause with Sum()

You can use the sum() as you have used the COUNT() with the HAVING clause in the previous example. This sum() computes the sum of the numeric column of the table and the HAVING clause filters the value of these aggregated results based on the specified condition.

For example, you will use the same table ‘orders’ and suppose you want to find the total items ordered by a customer which is more than 5.

Use the below query.

SELECT customer_id, SUM(total_items) AS total_ordered_items
FROM orders
GROUP BY customer_id
HAVING SUM(total_items) > 5;

In the above syntax, (GROUP BY customer_id) groups the rows by customer_id and the SUM(total_tiems) function computes the total number of items ordered by each customer and gives an alias as total_orders_items. Then (HAVING COUNT(total_items) > 5) clause filters the customers with total items ordered more than 5.

Using Postgresql Having Clause with MIN()

The MIN() function of PostgreSQL returns the minimum value from a specified column and you can use this function with the HAVING clause to filter the group result based on the given condition which involves the minimum value.

Let’s say you want to find customers with less than 3 items, then use the below query.

SELECT customer_id, MIN(total_items) AS min_ordered_items
FROM orders
GROUP BY customer_id
HAVING MIN(total_items) < 3;

In the above syntax, (GROUP BY customer_id) groups the rows by customer_id and the MIN(total_tiems) function computes the minimum number of items ordered by each customer and gives an alias as min_ordered_items. Then (the HAVING COUNT(total_item) < 3) clause filters the customers with minimum items ordered less than 3.

Using Postgresql Having Clause with MAX()

The MAX() function of PostgreSQL returns the maximum value from a column and you can use this function with the HAVING clause to filter the group result based on the given condition which involves the maximum value.

Suppose you have table ‘products’ with columns ‘product_id’, ‘product_name’, ‘price’, and ‘order_country’, the table is shown below.

As you can see table contains the product details like product_id (1), product_name (Apple iPhone 13), price (999.0), and order_coutry (USA). Now you want to find the countries where the product price is more than $1000.

Use the below query.

SELECT order_country, MAX(price) AS max_product_price
FROM products
GROUP BY order_country
HAVING MAX(price) > 1000;

In the above syntax, (GROUP BY order_country) groups the rows by column order_country and the MAX(price) function computes the maximum product price for each country and gives an alias as max_product_price. Then (the HAVING MAX(price) > 1000) clause filters the countries with maximum product price greater than $1000.

Conclusion

In this PostgreSQL tutorial, you learned how to use the HAVING clause in PostgreSQL to filter the group-based result. Also, you learned how to use the aggregate functions with the HAVING clause.

You may also read:

• PostgreSQL group by with examples
• PostgreSQL Order By with Examples
• How to use Postgres Min() Function