Restricting Group Results

• In the same way that you use the WHERE clause to restrict the rows that you select, you use the HAVING clause to restrict groups

• To find the maximum salary in each of the departments that have a maximum salary greater than $10,000, you need to do the following :

1. Find the average salary for each department by groupingby department number
2. Restrict the groups to those departments with a maximum salary greater than $10,000

Restricting Group Results with the HAVING Clause :

• When you use the HAVING clause, the PostgreSQL server restricts groups as follows:

1. Rows are grouped
2. The group function is applied
3. Groups matching the HAVING clause are displayed

SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
Example :

• SELECT deptno, MAX(sal) FROM emp GROUP BY deptno HAVING MAX(sal)>4000 ;
• SELECT job, SUM(sal) as PAYROLL FROM emp WHERE job NOT LIKE '%MAN%' GROUP BY job HAVING SUM(sal) > 4000 ORDER BY SUM(sal);

SELECT column, group_function FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];