Examples of Rollup and Grouping Sets in Oracle

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 

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 

Hope helps and thank you for reading.