PostgreSQL Having Clause | PostgreSQL Tutorials and Solutions

In this article, we will see PostgreSQL Having Clause Syntax with examples.

1. PostgreSQL HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE.
2. HAVING can be used only with the SELECT statement. HAVING is typically used with a GROUP BY clause.

PostgreSQL Having Syntax:

SELECT select_list
FROM table_name
GROUP BY group_list
HAVING conditions;

1. Get the employees department wise where average salary greater than 1000.

select deptno, avg(sal) from emp group by deptno
having avg(sal)<2000;

2. Calculate total salaries paid to each department where deptno = 10 or 20 and sum salary less than 10000

select deptno, sum(sal) as sum_sal from emp
where deptno in(10,20)
group by deptno
having sum(sal)<10000;

3. Calculate number of employees joined in each year where year is 1980,1981 and no of employees greater than 2.

select extract(ISOYEAR from hiredate) as Year, count(*) as emps from emp
where extract(ISOYEAR from hiredate) in(1980,1981)
group by extract(ISOYEAR from hiredate)
having count(*)>2;

4. Select employees maximum and minimum salaries when maximum salary is greater than 2000 in each department.

select deptno, max(sal), min(sal) from emp group by deptno
 having max(sal)>2000;