</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:18735507471922#18844335353167 <code>
You use grouping_id with "grouping sets" or group by cube/rollup -- it tells you the level of aggregation.
Consider, we'll show grouping sets, group by rollup and group by cube to see what you get:
ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
Table created.
ops$tkyte@ORA9IR2> select deptno, job, sum(sal),
2 grouping_id(deptno) deptno_g,
3 grouping_id(job) job_g,
4 case when grouping_id(deptno)||grouping_id(job) = '00'
5 then 'Dtl both'
6 when grouping_id(deptno)||grouping_id(job) = '10'
7 then 'Agg over deptno'
8 when grouping_id(deptno)||grouping_id(job) = '01'
9 then 'Agg over job'
10 when grouping_id(deptno)||grouping_id(job) = '11'
11 then 'Agg over both'
12 end what
13 from emp
14 group by grouping sets( (deptno), (job) )
15 /
DEPTNO JOB SUM(SAL) DEPTNO_G JOB_G WHAT
------ --------- ---------- ---------- ---------- ---------------
10 8750 0 1 Agg over job
20 10875 0 1 Agg over job
30 9400 0 1 Agg over job
ANALYST 6000 1 0 Agg over deptno
CLERK 4150 1 0 Agg over deptno
MANAGER 8275 1 0 Agg over deptno
PRESIDENT 5000 1 0 Agg over deptno
SALESMAN 5600 1 0 Agg over deptno
8 rows selected.
We asked for group bys only on DEPTNO and then only on JOB. So, that was like running the query:
select deptno, null, sum(sal) from emp group by deptno
union all
select null, job, sum(sal) from emp group by job;
in one query.... -- grouping_id tells us what level of detail we have on each rowops$tkyte@ORA9IR2> select deptno, job, sum(sal),
2 grouping_id(deptno) deptno_g,
3 grouping_id(job) job_g,
4 case when grouping_id(deptno)||grouping_id(job) = '00'
5 then 'Dtl both'
6 when grouping_id(deptno)||grouping_id(job) = '10'
7 then 'Agg over deptno'
8 when grouping_id(deptno)||grouping_id(job) = '01'
9 then 'Agg over job'
10 when grouping_id(deptno)||grouping_id(job) = '11'
11 then 'Agg over both'
12 end what
13 from emp
14 group by rollup( deptno, job )
15 /
DEPTNO JOB SUM(SAL) DEPTNO_G JOB_G WHAT
------ --------- ---------- ---------- ---------- ---------------
10 CLERK 1300 0 0 Dtl both
10 MANAGER 2450 0 0 Dtl both
10 PRESIDENT 5000 0 0 Dtl both
10 8750 0 1 Agg over job
20 CLERK 1900 0 0 Dtl both
20 ANALYST 6000 0 0 Dtl both
20 MANAGER 2975 0 0 Dtl both
20 10875 0 1 Agg over job
30 CLERK 950 0 0 Dtl both
30 MANAGER 2850 0 0 Dtl both
30 SALESMAN 5600 0 0 Dtl both
30 9400 0 1 Agg over job
29025 1 1 Agg over both
13 rows selected.
Rollup is sort of like a running total report -- and grouping id tells us when the rollups happened. So the data is sorted by deptno, job and we have subtotals by job (agg over job) and by deptno, job (agg over both) along with the details by deptno/job
cube is similar but you get all possible aggregations: ops$tkyte@ORA9IR2> select deptno, job, sum(sal),
2 grouping_id(deptno) deptno_g,
3 grouping_id(job) job_g,
4 case when grouping_id(deptno)||grouping_id(job) = '00'
5 then 'Dtl both'
6 when grouping_id(deptno)||grouping_id(job) = '10'
7 then 'Agg over deptno'
8 when grouping_id(deptno)||grouping_id(job) = '01'
9 then 'Agg over job'
10 when grouping_id(deptno)||grouping_id(job) = '11'
11 then 'Agg over both'
12 end what
13 from emp
14 group by cube( deptno, job )
15 /
DEPTNO JOB SUM(SAL) DEPTNO_G JOB_G WHAT
------ --------- ---------- ---------- ---------- ---------------
29025 1 1 Agg over both
CLERK 4150 1 0 Agg over deptno
ANALYST 6000 1 0 Agg over deptno
MANAGER 8275 1 0 Agg over deptno
SALESMAN 5600 1 0 Agg over deptno
PRESIDENT 5000 1 0 Agg over deptno
10 8750 0 1 Agg over job
10 CLERK 1300 0 0 Dtl both
10 MANAGER 2450 0 0 Dtl both
10 PRESIDENT 5000 0 0 Dtl both
20 10875 0 1 Agg over job
20 CLERK 1900 0 0 Dtl both
20 ANALYST 6000 0 0 Dtl both
20 MANAGER 2975 0 0 Dtl both
30 9400 0 1 Agg over job
30 CLERK 950 0 0 Dtl both
30 MANAGER 2850 0 0 Dtl both
30 SALESMAN 5600 0 0 Dtl both
18 rows selected.
so, grouping id doesn't avoid multiple grouping functions (grouping SETS does). but grouping id plays an important role in seeing what data is "what"