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];

Illegal Queries - Using Group Functions

• Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause

• SELECT deptno, COUNT(ename) FROM emp;
• ERROR : column "emp.deptno" must appear in the GROUP BY clause or be used in an aggregate function SQL state: 42803
• Column missing in the GROUP BY clause

• You cannot use the WHERE clause to restrict groups
• You use the HAVING clause to restrict groups
• You cannot use group functions in the WHERE clause

• SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal) > 8000 GROUP BY deptno;
• WHERE AVG(salary) > 8000
  ERROR : aggregates not allowed in WHERE clause
• Cannot use the WHERE clause to restrict groups