PostgreSQL – HAVING Clause

‘HAVING’ clause is used to filter results based on aggregate functions. It is used to filter the results that appear from a ‘GROUP BY’ clause. ‘HAVING’ cannot be used without ‘GROUP BY’.

syntax:

SELECT col_name, agg_func(column) from table_name GROUP BY col_name HAVING agg_func(column) condition;

Consider the employee table as shown below:

id	first_name	last_name	department	salary
101	bryan	walker	sales	50,000
102	alex	carpenter	accounts	55,000
103	sam	white	sales	60,000
104	john	barry	sales	54,000
105	bill	wood	accounts	65,000
106	robert	hopkins	support	70,000
107	david	lee	support	72,000
108	jack	sears	support	65,000
109	tom	young	sales	55,000

sql to get the count of employees in each department is as shown below:

SELECT department, count(id) FROM employee GROUP BY department;

department    count
----------   ------
sales        4
accounts     2
support      3

If you want to view only the departments that have more than 3 employees, you can use the ‘HAVING’ clause. Here is the query:

SELECT department, count(id) FROM employee GROUP BY department HAVING count(id)>3;

department    count
----------   ------
sales        4