Clarification on Grouping_ID in 11g

sam_2012
sam_2012 used Ask the Experts™
on
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.

  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.

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
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

Author

Commented:
Hi Stuber,

How Grouping_id generates this number i.e 0,1,2,3 ?
Most Valuable Expert 2011
Top Expert 2012

Commented:
given (a,b)

0 - a,b
1 -  a
2 - b
3 - (all)

it's simply a counter across the various combinations of the fields.

Author

Commented:
awesome

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial