On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Friday, June 13, 2008

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

No comments:

Post a Comment