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:

idfirst_namelast_namedepartmentsalary
101bryanwalkersales50,000
102alexcarpenteraccounts55,000
103samwhitesales60,000
104johnbarrysales54,000
105billwoodaccounts65,000
106roberthopkinssupport70,000
107davidleesupport72,000
108jacksearssupport65,000
109tomyoungsales55,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

See also  PostgreSQL - Rename Constraint