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