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.
database functions HAVING sql clause sql query