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.

Select all Open in new window