YogeshChauhan . com

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.

amazon

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

Full and Partial CUBE in Postgres with ExamplesPostgresAvoid using new Array() in JavaScriptJavaScriptHow to show widgets on the Appearance tab in WordPress?WordPressReverse a String in JavaScriptJavaScriptLearn how to add Scroll Indicator using CSS and JavaScript?CSSA Quick Comparison of JOIN and Subquery in SQLSQL/MySQL