We help IT Professionals succeed at work.
Get Started

Clarification on Grouping_ID in 11g

sam_2012
sam_2012 asked
on
62 Views
Last Modified: 2016-01-31
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
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE