Grouping Sets

GROUPING SETS allow a set of records to be grouped in multiple ways. In these reports, we are looking for a total of the salary field based on job and department, or department only, or job only. The '( )' is used to indicate a total for the report.

(In these examples, the Where excludes department 30 just to reduce output.)

---

This report shows two groupings:

• the sum of salary based on DeptNo & Job
• total by DeptNo
  

 Select DeptNo, Job, Sum(Sal)
  From Emp
 Where Deptno != 30
 Group By               GROUPING SETS ( (DeptNo, Job),
                                         DeptNo
                                       )
 Order By DeptNo, Job;

---

This report shows two groupings:

• sum of each DeptNo
  
• then the sum of each Job

 Select DeptNo, Job, Sum(Sal)
  From Emp
 Where Deptno != 30
 Group By               GROUPING SETS (  DeptNo,
                                         Job
                                      )
 Order By DeptNo, Job;

---

This report shows four groupings:

• Sum of Salary based on DeptNo & Job
• the total for each DeptNo
  
• the total for each Job
• Total for report - indicated by ( )
  

 Select DeptNo, Job, Sum(Sal)
  From Emp
 Where Deptno != 30
 Group By               GROUPING SETS ( (DeptNo, Job),
                                         DeptNo,
                                         Job   ,
                                         ( )
                                       )
Order By DeptNo, Job;

See Oracle documentation for further detail:
Grouping Sets