troubleshooting Question

Clarification on Grouping_ID in 11g

Avatar of sam_2012
sam_2012 asked on
Oracle Database
4 Comments1 Solution66 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros