Clarification on Grouping_ID in 11g
Hi Team,
I need a clarification on how grouping_id works in a query involving
rollup or cube or grouping sets . My question is in the below query how grouping_id is determining the level for each aggregation mentioned in the query . IN the last row level is 3 , but 2 is missing. How this number is derived by grouping_id function.
I need a clarification on how grouping_id works in a query involving
rollup or cube or grouping sets . My question is in the below query how grouping_id is determining the level for each aggregation mentioned in the query . IN the last row level is 3 , but 2 is missing. How this number is derived by grouping_id function.
1 Select Department_id , JOB_ID , SUM(SALARY) TotalSalary, grouping_id(department_id,job_id) lvl
2 from employees
3* group by rollup(department_id,JOB_ID)
SQL> /
DEPARTMENT_ID JOB_ID TOTALSALARY LVL
------------- ---------- ----------- ----------
SA_REP 7000 0
IT_PROG 0
7000 1
10 AD_ASST 4400 0
10 4400 1
20 MK_MAN 13000 0
20 MK_REP 6000 0
20 19000 1
30 PU_MAN 11000 0
30 PU_CLERK 13900 0
30 24900 1
40 HR_REP 6500 0
40 6500 1
50 ST_MAN 36400 0
50 SH_CLERK 64300 0
50 ST_CLERK 55700 0
50 156400 1
60 IT_PROG 28800 0
60 28800 1
70 PR_REP 10000 0
70 10000 1
80 SA_MAN 61000 0
80 SA_REP 243500 0
80 304500 1
90 AD_VP 34000 0
90 AD_PRES 24000 0
90 58000 1
100 FI_MGR 12008 0
100 FI_ACCOUNT 39600 0
100 51608 1
110 AC_MGR 12008 0
110 AC_ACCOUNT 8300 0
110 20308 1
691416 3
34 rows selected.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
It's probably easier to explain by doing a comparison.
replace ROLLUP, with CUBE
level 0 is sum for dept and job
level 1 is sum for a dept
level 2 is sum for a job
level 3 is sum for everything
no try ROLLUP again with the order of the columns reversed (job,dept) instead of (dept,job)
you'll see level 2 is still missing, but now the sums for level 1 for jobs, not departments
cube iterates through all possible combinations
rollup iterates backwards though the list of columns, removing one item for each level.
So, rollup of (dept,job) never sees the combination of summing on job with null department
replace ROLLUP, with CUBE
level 0 is sum for dept and job
level 1 is sum for a dept
level 2 is sum for a job
level 3 is sum for everything
no try ROLLUP again with the order of the columns reversed (job,dept) instead of (dept,job)
you'll see level 2 is still missing, but now the sums for level 1 for jobs, not departments
cube iterates through all possible combinations
rollup iterates backwards though the list of columns, removing one item for each level.
So, rollup of (dept,job) never sees the combination of summing on job with null department
Premium Content
You need an Expert Office subscription to comment.Start Free Trial