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
  

Creating Groups of Data

GROUP BY Clause Syntax :

• SELECT column, group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column];

• You can divide rows in a table into smaller groups by using the GROUP BY clause

Using the GROUP BY Clause :

• All columns in the SELECT list that are not in group functions must be in the GROUP BY
  clause
• SELECT deptno, AVG(sal) FROM emp GROUP BY deptno ;

• The GROUP BY column does not have to be in the SELECT list
• SELECT AVG(sal) FROM emp GROUP BY deptno ;

Grouping by More Than One Column :

• You can also group by multiple columns
• Example : select deptno, job, avg(sal) from emp group by deptno, job;