PostgreSQL HAVING Clause

In PostgreSQL, the HAVING clause is used after the GROUP BY clause to filter the result of the GROUP BY clause based on the specified condition. The HAVING condition is the same as the WHERE condition that returns TRUE or FALSE.

The columns used in the HAVING condition must be used in the GROUP BY clause unless an aggregate function is used with the column. Currently, FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE, and FOR KEY SHARE cannot be specified with HAVING.

Syntax:

SELECT <column_list>
FROM <table_name>
[ WHERE <conditions>]
[ GROUP BY <column1>, <column2>...]
[ HAVING condition ]
[ ORDER BY ]

PostgreSQL evaluates the HAVING clause after FROM, WHERE, GROUP BY but before SELECT, ORDER BY, and LIMIT clauses. The HAVING clause is evaluated before the SELECT clause, so the column alias cannot be used in the HAVING clause.

Let's see how to use the GROUP BY HAVING clause using the following employee table.

The following query gets the total salary for each department using the GROUP BY clause.

Example: GROUP BY

SELECT dept_id, SUM(salary) AS "Total Salary" FROM employee
GROUP BY dept_id;

The above query displays the following result in pgAdmin:

Now, use the HAVING clause to filter the grouped result further. For example, the following returns the rows where the total salary is more than 200000 using the HAVING clause.

Example: HAVING Clause

SELECT dept_id, SUM(salary) AS "Total Salary" FROM employee
GROUP BY dept_id
HAVING SUM(salary) &gt; 200000;

Note that the group is formed on the dept_id column and the SELECT clause uses the aggregate function SUM() to get the total salary for each dept_id. The HAVING clause also uses the SUM() function in the condition to filter the grouped data because the salary column is not used with the GROUP BY clause. If you don't use any aggregate function then it will display the following error.

HAVING clause with Multiple Columns

You can specify multiple conditions in the HAVING clause. Here, for each department, we will find the number of employees and maximum salary been paid to employees using the GROUP BY clause. The HAVING clause will be applied on grouped rows to fetch only departments that have more than 3 employees and a maximum salary of more than 75000.

Example: Multi-columns HAVING Clause

SELECT dept_id, COUNT(emp_id) AS "No of Employees", MAX(salary) as "Max Salary"
FROM employee
GROUP BY dept_id
HAVING COUNT(emp_id) > 3 AND MAX(salary) &gt; 75000;

HAVING clause with WHERE clause

The HAVING clause can be used along with the WHERE clause also. The WHERE clause conditions are applied first on individual rows to filter out data, after that GROUP BY does the grouping of data and the HAVING clause to filter grouped data.

Let's find out which department's Male employees' total salary is more than 100000. First filter out only Male employees using the WHERE clause and then for each department get SUM of Salary and finally select department using HAVING clause.

Example: WHERE & HAVING

SELECT dept_id, SUM(salary) FROM Employee
WHERE gender = 'M'
GROUP By dept_id
HAVING SUM(salary) &gt; 100000;