Postgresql Having Clause + Examples

In this PostgreSQL tutorial, we will learn about Postgresql having clauses and cover the following topics.

• Postgresql having clause
• Postgresql having count greater than
• Postgresql having max
• Postgresql having min
• Postgresql having without group by
• Postgresql having function
• Postgresql having vs where
• Postgresql having subquery
• Postgresql having distinct

Postgresql having clause

In Postgresql, the Having clause is used as a search condition for an aggregate function or a group. To filter the groups or aggregate based on a given or stated criteria, we’ll utilize the HAVING clause with the GROUP BY clause.

The syntax of the HAVING clause is given below.

SELECT
	col_1,
	aggregate_func (col_2)
FROM
	table_name
GROUP BY
	col_1
HAVING
	condition;

In the above syntax, Group by clause returns rows grouped by col_1, the Having clause will filter, this groups based on a specified condition.

Also, read: Postgresql while loop

Postgresql having count greater than

In Postgresql, with the help of the HAVING clause, we can filter the result returned by the count function which is greater than some specified value.

The table we will use in this tutorial is employe and the description of the table is given below.

Now, will count the first name of the employees using the count function.

SELECT first_name, count(first_name) FROM employee
GROUP BY first_name;

In the above code, we are counting the first_name of the employee using the aggregate function named COUNT(), and grouping it by first_name.

The output of the above code is given below.

In the above output, we can see that Divine is the first_name of 230 employees and so on.

Let’s filter the employees whose first_name is greater than 240 using the having clause.

SELECT first_name, count(first_name) FROM employee
GROUP BY first_name
HAVING count(emp_no) > 240;

In the above code, we are filtering the result using the having clause, which will show the first_name of employees having first_name greater than 240.

In the above output, it shows the employee’s first_name greater than 240 like Masaru has 242.

Also, check: Postgresql REGEXP_REPLACE Function

Postgresql having max

In Postgresql, If we use the MAX() function in a HAVING clause, we can apply a filter for a group.

Let’s run the following query that selects only the highest salary paid to each employee and the salaries are greater than 28000.

SELECT name, max(salary_$) FROM emp_info
GROUP BY age, name
HAVING max(salary_$) > 28000;

The output of the above code is given below.

In the above output, Rony, Lillian, Dan has salaries greater than 28000.

Let’s run the max function with having on another table, here we have a table named major_cities that contains the city name of the United Kingdom and the population of that city.

Now, we will filter the city which has the maximum population.

SELECT city_name, max(population) FROM major_cities
GROUP BY city_name
HAVING max(population) > 580000;

The code will filter the city name whose population is greater than 580000.

There are five cities in the United Kindom whose population is greater than 580000 and the city name is Briston, Birmingham, London, Glasgow, Liverpool.

Read: Postgresql escape single quote

Postgresql having min

In Postgresql, If we use the MIN() function in a HAVING clause, we can apply a filter for a group. The MIN function returns the minimum value from the column or list of values.

Let’s run the following query that selects only the Lowest salary paid to each employee and the salaries are less than 45000.

SELECT name, min(salary_$) FROM emp_info
GROUP BY age, name
HAVING max(salary_$) < 28000;

The output of the above code is given below.

In the above code, Gregory, Iris, Robert has minimum a salary of less than 28000.

Read: Postgresql function return table

Postgresql having without group by

In Postgresql, we can use the having clause without group by clause, let’s run the below simple example.

select (2*2) having 2 =2;

In the above code, it will return the select statement result, if having condition is true, otherwise returns nothing.

Remember, the HAVING clause is executed after FROM, WHERE, GROUP BY, and before SELECT, DISTINCT, ORDER BY, LIMIT clause.

The output of the above code is given below.

In the above output, the result returned by the SELECT statement is 4, because the HAVING condition becomes true.

Read: PostgreSQL Date Difference

Postgresql having vs where

In Postgresql, there is a difference between having and where clause.

Where clause is used to filter the row while having clause for a group of rows.

Let’s understand through an example.

SELECT name FROM emp_info
WHERE salary_$ >30000;

In the above code, we are filtering the name of employees based on the condition salary_$ > 30000 using the WHERE clause. The condition is applied on each row one by one.

The output of the above code is given below.

Now, we will get the same result as the above one using the HAVING clause.

SELECT name,max(salary_$) FROM emp_info
GROUP BY salary_$,name
HAVING max(salary_$) >30000;

The output of the above code is given below.

In the above output, the result is the same as we got using the WHERE clause.

Read: PostgreSQL WHERE with examples

Postgresql having subquery

In Postgresql, we can use the having clause with subquery, a subquery is a query within a query.

Let’s run the below query.

SELECT name,max(salary_$) FROM emp_info
GROUP BY salary_$,name
HAVING max(salary_$) > (SELECT salary_$ FROM emp_info
		        WHERE salary_$ = 45000);

The output of the above code is given below.

Postgresql having distinct

In Postgresql, we can use the distinct function with the Having clause, the distinct function returns the unique values from a column.

The table that we will use here is data_info and the description of the table is given below.

Let’s run the below query that has a distinct function with the HAVING clause.

Select Type, Color, Count(Distinct Location) As UniqueLocations
From data_info
Group By Type, Color
Having Count(Distinct Location) > 1

The output of the above code is given below.

Also, take a look at some more PostgreSQL tutorials.

• PostgreSQL WHERE IN with examples
• How to migrate from MySQL to Postgres
• Postgresql current_timestamp
• Postgresql Format
• Postgresql Add Foreign Key
• Postgresql listen_addresses
• Postgresql import SQL file

So, in this tutorial, we have learned about Postgresql having a clause. And we have also covered the following topics.

• Postgresql having clause
• Postgresql having count greater than
• Postgresql having max
• Postgresql having min
• Postgresql having without group by
• Postgresql having function
• Postgresql having vs where
• Postgresql having subquery
• Postgresql having distinct