Examples of Rollup and Grouping Sets in Oracle

Posted By  Nimish Garg On 17 Jun 2011 09:06:26
emailbookmarkadd commentsprint
No of Views:2219
Bookmarked:0 times
Votes:0 times

Introduction

In this tip, I'm going to give few examples Rollup and Grouping Set in Oracle.

Rollup

select nvl(dname,'ALL DEPARTMENTS') dname, nvl(job,'ALL JOBS') job, sum(sal) sal
from scott.emp e, scott.dept d
where d.deptno = e.deptno
group by rollup(dname, job)

Output 

Image Loading

GROUPING SETS

select nvl(dname,'ALL DEPARTMENTS') dname, nvl(job,'ALL JOBS') job, sum(sal) sal
from scott.emp e, scott.dept d
where d.deptno = e.deptno
group by grouping sets(dname, (dname, job))

Output 

Image Loading

Hope helps and thank you for reading.

Sign Up to vote for this article
Other popular Tips/Tricks
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top