HAVING Clause in Postgres with Examples in All Aggregate Functions

HAVING clause is helpful in eliminating group(s) of rows which do not satisfy a specific condition.

We saw GROUP BY clause in this post: Order By And Group By In Postgres

HAVING goes along with GROUP BY so the syntax looks like this:


SELECT column1, aggregate_function (column2)
FROM table
GROUP BY column1
HAVING condition;

We saw the difference between HAVING and WHERE in this post: The Fundamental Difference Between HAVING And WHERE Clauses In Postgres

WHERE selects input rows before groups and aggregates are computed, whereas HAVING selects group rows after groups and aggregates are computed.

We can use the HAVING clause without the GROUP BY clause in Postgres.

In that scenario, the HAVING will refer to only one group as we haven't divided the rows into groups using GROUP BY.

Examples:

I am using the database available on my Github public repo

We can use HAVING with any aggregate function so let's use with all of them.

If you don't know much about aggregate functions, I've this post with complete basic examples: Aggregate Functions Examples In Postgres For Beginners

HAVING with MAX


SELECT category_id, MAX(unit_price)
    FROM products
    GROUP BY category_id
	HAVING MAX(unit_price) > 20;

//Output
category_id, max
8	62.5
7	53
1	263.5
...
...

In this example, we are getting the category_id and maximum unit_price from each category from the products table. Then discarding the rows which has maximum unit_price less than 20. We are keeping the rows with unit_price greater than 20.

HAVING with MIN


SELECT category_id, MIN(unit_price)
    FROM products
    GROUP BY category_id
	HAVING MIN(unit_price) < 5;

//Output
category_id, min
1	4.5
4	2.5

In this example, we are getting the category_id and minimum unit_price from each category from the products table. Then discarding the rows which has minimum unit_price greater than 5. We are keeping the rows with unit_price less than 5.

HAVING with COUNT


SELECT category_id, COUNT(product_id)
    FROM products
    GROUP BY category_id
	HAVING COUNT(product_id) < 10;

//Output
category_id, count
7	5
5	7
6	6

In this example, we are getting the category_id and counting products from each category from the products table. Then discarding the rows which has greater than 10 products. We are keeping the rows with less than 10 products.

HAVING with AVG


SELECT category_id, AVG(unit_price)
    FROM products
    GROUP BY category_id
	HAVING AVG(unit_price) > 50;

//Output
category_id, avg
6	54.00666666030884

In this example, we are getting the category_id and average unit_price from each category from the products table. Then discarding the rows which has average unit_price less than 50. We are keeping the rows with average unit_price greater than 50.

HAVING with SUM


SELECT category_id, SUM(unit_price)
    FROM products
    GROUP BY category_id
	HAVING SUM(unit_price) < 150;

//Output
category_id    sum
5	141.75

In this example, we are getting the category_id and sum of unit_price from each category from the products table. Then discarding the rows which has sum of unit_price greater than 150. We are keeping the rows with the sum of unit_price less than 150.

« Previous Next »