Yogesh Chauhan's Blog

HAVING Clause in Postgres with Examples in All Aggregate Functions

in Postgres on 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.


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 How to add Read More Read Less Button using JavaScript? #3 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #4 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
How to add a new role in WordPress?WordPressArbitrary Arguments in SCSS functionsSCSSClasses in JavaScript: The BasicsJavaScript@forward modules with a prefix in SCSS (Sass)SCSSIntroduction to components and templates Part 4: Pipes and DirectivesAngularclip and clip-path properties in CSSCSS