Clarification on Grouping

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.

  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

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 75

sdstuberCommented: 2016-01-30

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

sam_2012Author Commented: 2016-01-30

Hi Stuber,

How Grouping_id generates this number i.e 0,1,2,3 ?

given (a,b)

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

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

sam_2012Author Commented: 2016-01-31

awesome

Clarification on Grouping_ID in 11g

Who is Participating?