YogeshChauhan.com
HAVING Clause in Postgres with Examples in All Aggregate Functions
May 9, 2020

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.

dreamhost

Leave a Reply

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 How to set opacity or transparency using CSS? #3 Pagination in CSS with multiple examples #4 How to make HTML form interactive and using CSS? #5 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

Mar 5 How to create flickering text using CSS? Mar 4 How to use data-* Attributes in HTML? Mar 4 The substr() method in JavaScript and how it’s different from substring() Mar 4 A complete guide to add responsive YouTube videos using HTML and CSS Mar 3 How to embed YouTube or other video links in WordPress? Mar 3 How to change the Login Logo in WordPress?

You might also like these

4 different Ways to Get JavaScript OutputJavaScriptHow to set up the local environment and workspace for Angular development?AngularHow to get category name using post id in WordPress?WordPressHere’s what we can do with PHP date() functionPHPNumber Properties in JavaScriptJavaScriptHow to Validate User Name, Email Address and URL in PHP?PHP