Using group by on multiple columns?

Question

I understand the point of `GROUP BY x` But how does `GROUP BY x, y` work, and what does it mean?

Answer 1

Say we want to see the average salary for each job title within each department. We would start with the following query:

SQL> SELECT DEPTNO, JOB, AVG(SAL)

FROM EMP;

ORA-00937: not a single-group group function

This Oracle error is the result of using the GROUP FUNCTION AVG with two columns - DEPTNO and JOB. The columns want to return many times, and AVG wants to return once. So, the solution is to add a "GROUP BY" clause.

SQL> SELECT DEPTNO, JOB, AVG(SAL)

FROM EMP

GROUP BY DEPTNO;

ORA-00979: not a GROUP BY expression

For the past 31 years I have told my students, DEPTNO and JOB are like two problem children in this query. They both want to return many times, and the GROUP FUNCTION AVG wants to return once. We wrote a GROUP BY for DEPTNO, but we ALSO NEED ONE FOR JOB before this query will execute.

SQL> SELECT DEPTNO, JOB, AVG(SAL)

FROM EMP

GROUP BY DEPTNO, JOB

ORDER BY DEPTNO;

This GROUP BY clause tells Oracle to group the employees by department number, and within each department also group by job.

Then, Oracle can calculate the AVERAGE SALARY BY EACH JOB FOR EACH DEPARTMENT.

DEPTNO JOB AVG(SAL)

------------ ----------- --------------

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 6789

20 ANALYST 3000

20 CLERK 950

20 MANAGER 2975

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 2850

Another example. Since it is getting close to CENSUS taking time, let's write a query that shows the number of people living in a STATE, COUNTY, and CITY. We will have three columns in our SELECT statement, and the COUNT function.

SQL> SELECT STATE, COUNTY, CITY, COUNT(*)

FROM CENSUS

GROUP BY STATE, COUNTY, CITY

ORDER BY STATE, COUNTY, CITY;

Here, Oracle has to group the rows by STATE, COUNTY, and CITY before the COUNT(*) function can be applied to the rows in each grouping. If we don't group by all three of the columns in the SELECT clause, we receive an error because of the COUNT(*) function.