Solved

usage of grouping_id rollup

Posted on 2007-04-05
2
501 Views
Last Modified: 2007-12-19
Can some one provide a good example of usage of grouping_id rollup ?  

This article  http://www.revealnet.com/newsletter-v6/0705_B.htm has something, but not good enough.

I like to see more columns are used and how the DECODE (GROUPING_ID like the following works out.  Thanks.

DECODE (GROUPING_ID (line_lvl_1,
                                                     line_lvl_2,
                                                     line_lvl_3,
                                                     line_lvl_4,
                                                    ),
                                        15, 4,
                                        7, 5,
                                        3, 6,
                                        1, 7,
                                        0, 8
                                       ),

0
Comment
Question by:ewang1205
  • 2
2 Comments
 
LVL 23

Accepted Solution

by:
paquicuba earned 500 total points
ID: 18859774
If you know how GROUPING works, then all you have to do is create a bit vector of the this function and you'll get the GROUPING_ID, which is used to eliminate rows that don't meet a certain criteria, see the example below:

Simple SELECT statement:

  1  SELECT YEAR, MONTH, REGION_NAME, SALARY
  2  FROM
  3  (SELECT
  4  EXTRACT(YEAR FROM HIRE_DATE) YEAR,
  5  EXTRACT(MONTH FROM HIRE_DATE) MONTH,
  6  R.REGION_NAME,
  7  SALARY
  8  FROM EMPLOYEES E, LOCATIONS L, DEPARTMENTS D, COUNTRIES C, REGIONS R
  9  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
 10  AND D.LOCATION_ID = L.LOCATION_ID
 11  AND L.COUNTRY_ID = C.COUNTRY_ID
 12  AND C.REGION_ID = R.REGION_ID)
 13  WHERE YEAR > 1998
 14* ORDER BY 1,2,3
HR@PROD > /

      YEAR MONTH REGION_NAME  SALARY

      1999 1 Americas      2400
      1999 2 Americas      3000
      1999 2 Americas      4200
      1999 2 Europe        7400
      1999 3 Americas      2800
      1999 3 Europe        7300
      1999 3 Europe        9500
      1999 4 Americas      2100
      1999 6 Americas      2500
      1999 6 Americas      2600
      1999 8 Americas      2500
      1999 10 Europe       11000
      1999 11 Americas      5800
      1999 11 Europe        7000
      1999 12 Americas      2400
      1999 12 Americas      6900
      1999 12 Americas      2500
      2000 1 Americas      2600
      2000 1 Europe        6200
      2000 1 Europe       10500
      2000 1 Europe        7200
      2000 2 Americas      2200
      2000 2 Americas      2800
      2000 2 Europe        6800
      2000 3 Americas      2200
      2000 3 Europe        6400
      2000 4 Europe        6100
      2000 4 Europe        6200

28 rows selected.

Elapsed: 00:00:00.03
HR@PROD > EDIT
Wrote file afiedt.buf




Simple agreegation of SALARY:



  1  SELECT YEAR, MONTH, REGION_NAME, SUM(SALARY) SALARY
  2  FROM
  3  (SELECT
  4  EXTRACT(YEAR FROM HIRE_DATE) YEAR,
  5  EXTRACT(MONTH FROM HIRE_DATE) MONTH,
  6  R.REGION_NAME,
  7  SALARY
  8  FROM EMPLOYEES E, LOCATIONS L, DEPARTMENTS D, COUNTRIES C, REGIONS R
  9  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
 10  AND D.LOCATION_ID = L.LOCATION_ID
 11  AND L.COUNTRY_ID = C.COUNTRY_ID
 12  AND C.REGION_ID = R.REGION_ID)
 13  WHERE YEAR > 1998
 14  GROUP BY YEAR, MONTH, REGION_NAME
 15* ORDER BY 1,2,3
HR@PROD > /

      YEAR MONTH REGION_NAME  SALARY
      1999 1 Americas      2400
      1999 2 Americas      7200
      1999 2 Europe        7400
      1999 3 Americas      2800
      1999 3 Europe       16800
      1999 4 Americas      2100
      1999 6 Americas      5100
      1999 8 Americas      2500
      1999 10 Europe       11000
      1999 11 Americas      5800
      1999 11 Europe        7000
      1999 12 Americas     11800
      2000 1 Americas      2600
      2000 1 Europe       23900
      2000 2 Americas      5000
      2000 2 Europe        6800
      2000 3 Americas      2200
      2000 3 Europe        6400
      2000 4 Europe       12300

19 rows selected.

Elapsed: 00:00:00.01
HR@PROD >


ROLLUP aggregation:



  1  SELECT YEAR, MONTH, REGION_NAME, SUM(SALARY) SALARY
  2  FROM
  3  (SELECT
  4  EXTRACT(YEAR FROM HIRE_DATE) YEAR,
  5  EXTRACT(MONTH FROM HIRE_DATE) MONTH,
  6  R.REGION_NAME,
  7  SALARY
  8  FROM EMPLOYEES E, LOCATIONS L, DEPARTMENTS D, COUNTRIES C, REGION
  9  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
 10  AND D.LOCATION_ID = L.LOCATION_ID
 11  AND L.COUNTRY_ID = C.COUNTRY_ID
 12  AND C.REGION_ID = R.REGION_ID)
 13  WHERE YEAR > 1998
 14  GROUP BY ROLLUP(YEAR, MONTH, REGION_NAME)
 15* ORDER BY 1,2,3
HR@PROD > /

      YEAR MONTH REGION_NAME  SALARY
      1999 1 Americas      2400
      1999 1               2400
      1999 2 Americas      7200
      1999 2 Europe        7400
      1999 2              14600
      1999 3 Americas      2800
      1999 3 Europe       16800
      1999 3              19600
      1999 4 Americas      2100
      1999 4               2100
      1999 6 Americas      5100
      1999 6               5100
      1999 8 Americas      2500
      1999 8               2500
      1999 10 Europe      11000
      1999 10             11000
      1999 11 Americas     5800
      1999 11 Europe       7000
      1999 11             12800
      1999 12 Americas    11800
      1999 12             11800
      1999                81900
      2000 1 Americas      2600
      2000 1 Europe       23900
      2000 1              26500
      2000 2 Americas      5000
      2000 2 Europe        6800
      2000 2              11800
      2000 3 Americas      2200
      2000 3 Europe        6400
      2000 3               8600
      2000 4 Europe       12300
      2000 4              12300
      2000                59200
                         141100

35 rows selected.


GROUPING: Returns 1 or 0
          1 = Null
          0 = Not Null



  1  SELECT YEAR, MONTH, REGION_NAME, SUM(SALARY) SALARY,
  2  GROUPING(YEAR) GY,
  3  GROUPING(MONTH) GM,
  4  GROUPING(REGION_NAME) GRN,
  5  GROUPING_ID(YEAR, MONTH, REGION_NAME) GID1,
  6  GROUPING_ID(MONTH, REGION_NAME, YEAR) GID2,
  7  GROUPING_ID(REGION_NAME, YEAR, MONTH) GID3,
  8  GROUP_ID()
  9  FROM
 10  (SELECT
 11  EXTRACT(YEAR FROM HIRE_DATE) YEAR,
 12  EXTRACT(MONTH FROM HIRE_DATE) MONTH,
 13  R.REGION_NAME,
 14  SALARY
 15  FROM EMPLOYEES E, LOCATIONS L, DEPARTMENTS D, COUNTRIES C, REGIONS R
 16  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
 17  AND D.LOCATION_ID = L.LOCATION_ID
 18  AND L.COUNTRY_ID = C.COUNTRY_ID
 19  AND C.REGION_ID = R.REGION_ID)
 20  WHERE YEAR > 1998
 21  GROUP BY ROLLUP(YEAR, MONTH, REGION_NAME)
 22* ORDER BY 1,2,3
HR@PROD > /

      YEAR      MONTH REGION_NAME     SALARY         GY         GM        GRN       GID1       GID2    GID3 GROUP_ID()
---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
      1999          1 Americas          2400          0          0          0          0          0       0             0
      1999          1                   2400          0          0          1          1          2       4             0
      1999          2 Americas          7200          0          0          0          0          0       0             0
      1999          2 Europe            7400          0          0          0          0          0       0             0
      1999          2                  14600          0          0          1          1          2       4             0
      1999          3 Americas          2800          0          0          0          0          0       0             0
      1999          3 Europe           16800          0          0          0          0          0       0             0
      1999          3                  19600          0          0          1          1          2       4             0
      1999          4 Americas          2100          0          0          0          0          0       0             0
      1999          4                   2100          0          0          1          1          2       4             0
      1999          6 Americas          5100          0          0          0          0          0       0             0
      1999          6                   5100          0          0          1          1          2       4             0
      1999          8 Americas          2500          0          0          0          0          0       0             0
      1999          8                   2500          0          0          1          1          2       4             0
      1999         10 Europe           11000          0          0          0          0          0       0             0
      1999         10                  11000          0          0          1          1          2       4             0
      1999         11 Americas          5800          0          0          0          0          0       0             0
      1999         11 Europe            7000          0          0          0          0          0       0             0
      1999         11                  12800          0          0          1          1          2       4             0
      1999         12 Americas         11800          0          0          0          0          0       0             0
      1999         12                  11800          0          0          1          1          2       4             0
      1999                             81900          0          1          1          3          6       5             0
      2000          1 Americas          2600          0          0          0          0          0       0             0
      2000          1 Europe           23900          0          0          0          0          0       0             0
      2000          1                  26500          0          0          1          1          2       4             0
      2000          2 Americas          5000          0          0          0          0          0       0             0
      2000          2 Europe            6800          0          0          0          0          0       0             0
      2000          2                  11800          0          0          1          1          2       4             0
      2000          3 Americas          2200          0          0          0          0          0       0             0
      2000          3 Europe            6400          0          0          0          0          0       0             0
      2000          3                   8600          0          0          1          1          2       4             0
      2000          4 Europe           12300          0          0          0          0          0       0             0
      2000          4                  12300          0          0          1          1          2       4             0
      2000                             59200          0          1          1          3          6       5             0
                                      141100          1          1          1          7          7          7          0

35 rows selected.



Depending on the position of the columns in the GROUPING_ID, you construct your vector, see below:


0      0      1      =      1            
0      0      0      =      0            
0      0      0      =      0            
0      0      1      =      1            
0      0      0      =      0            
0      0      1      =      1            
0      0      0      =      0            
0      0      0      =      0            
0      0      0      =      0            
0      0      0      =      0            
0      0      1      =      1            
0      0      0      =      0            
0      0      0      =      0            
0      0      0      =      0            
0      0      0      =      0            
0      0      0      =      0            
0      0      0      =      0            
0      0      0      =      0            
0      0      1      =      1            
0      0      0      =      0            
0      0      1      =      1            
0      0      0      =      0            
0      0      1      =      1            
0      0      1      =      1            
0      0      1      =      1            
0      0      0      =      0            
0      0      1      =      1            
0      0      1      =      1            
0      0      0      =      0            
0      0      1      =      1            
0      0      0      =      0            
0      0      1      =      1            
0      1      1      =      3            
0      1      1      =      3            
1      1      1      =      7            
                                    
                                    
                                    
                                    
0      1      0      =            2      
0      0      0      =            0      
0      0      0      =            0      
0      1      0      =            2      
0      0      0      =            0      
0      1      0      =            2      
0      0      0      =            0      
0      0      0      =            0      
0      0      0      =            0      
0      0      0      =            0      
0      1      0      =            2      
0      0      0      =            0      
0      0      0      =            0      
0      0      0      =            0      
0      0      0      =            0      
0      0      0      =            0      
0      0      0      =            0      
0      0      0      =            0      
0      1      0      =            2      
0      0      0      =            0      
0      1      0      =            2      
0      0      0      =            0      
0      1      0      =            2      
0      1      0      =            2      
0      1      0      =            2      
0      0      0      =            0      
0      1      0      =            2      
0      1      0      =            2      
0      0      0      =            0      
0      1      0      =            2      
0      0      0      =            0      
0      1      0      =            2      
1      1      0      =            6      
1      1      0      =            6      
1      1      1      =            7      
                                    
                                    
                                    
1      0      0      =                  4
0      0      0      =                  0
0      0      0      =                  0
1      0      0      =                  4
0      0      0      =                  0
1      0      0      =                  4
0      0      0      =                  0
0      0      0      =                  0
0      0      0      =                  0
0      0      0      =                  0
1      0      0      =                  4
0      0      0      =                  0
0      0      0      =                  0
0      0      0      =                  0
0      0      0      =                  0
0      0      0      =                  0
0      0      0      =                  0
0      0      0      =                  0
1      0      0      =                  4
0      0      0      =                  0
1      0      0      =                  4
0      0      0      =                  0
1      0      0      =                  4
1      0      0      =                  4
1      0      0      =                  4
0      0      0      =                  0
1      0      0      =                  4
1      0      0      =                  4
0      0      0      =                  0
1      0      0      =                  4
0      0      0      =                  0
1      0      0      =                  4
1      0      1      =                  5
1      0      1      =                  5
1      1      1      =                  7



0
 
LVL 23

Assisted Solution

by:paquicuba
paquicuba earned 500 total points
ID: 18860671
Here is a way to use DECODE. The GROUP_ID() is an extra feature that returns 1 if it finds a dupe.


  1  SELECT YEAR, MONTH, REGION_NAME, SUM(SALARY) SALARY,
  2  DECODE(GROUPING_ID(YEAR, MONTH, REGION_NAME),7,'TOP LEVEL',3,'TOP - 1 LEVEL','LOWER LEVEL') GID1,
  3  GROUP_ID()
  4  FROM
  5  (SELECT
  6  EXTRACT(YEAR FROM HIRE_DATE) YEAR,
  7  EXTRACT(MONTH FROM HIRE_DATE) MONTH,
  8  R.REGION_NAME,
  9  SALARY
 10  FROM EMPLOYEES E, LOCATIONS L, DEPARTMENTS D, COUNTRIES C, REGIONS R
 11  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
 12  AND D.LOCATION_ID = L.LOCATION_ID
 13  AND L.COUNTRY_ID = C.COUNTRY_ID
 14  AND C.REGION_ID = R.REGION_ID)
 15  WHERE YEAR > 1998
 16  GROUP BY ROLLUP(YEAR, MONTH, REGION_NAME)
 17* ORDER BY 1,2,3
HR@PROD > /

      YEAR      MONTH REGION_NAME                   SALARY GID1          GROUP_ID()
---------- ---------- ------------------------- ---------- ------------- ----------
      1999          1 Americas                        2400 LOWER LEVEL            0
      1999          1                                 2400 LOWER LEVEL            0
      1999          2 Americas                        7200 LOWER LEVEL            0
      1999          2 Europe                          7400 LOWER LEVEL            0
      1999          2                                14600 LOWER LEVEL            0
      1999          3 Americas                        2800 LOWER LEVEL            0
      1999          3 Europe                         16800 LOWER LEVEL            0
      1999          3                                19600 LOWER LEVEL            0
      1999          4 Americas                        2100 LOWER LEVEL            0
      1999          4                                 2100 LOWER LEVEL            0
      1999          6 Americas                        5100 LOWER LEVEL            0
      1999          6                                 5100 LOWER LEVEL            0
      1999          8 Americas                        2500 LOWER LEVEL            0
      1999          8                                 2500 LOWER LEVEL            0
      1999         10 Europe                         11000 LOWER LEVEL            0
      1999         10                                11000 LOWER LEVEL            0
      1999         11 Americas                        5800 LOWER LEVEL            0
      1999         11 Europe                          7000 LOWER LEVEL            0
      1999         11                                12800 LOWER LEVEL            0
      1999         12 Americas                       11800 LOWER LEVEL            0
      1999         12                                11800 LOWER LEVEL            0
      1999                                           81900 TOP - 1 LEVEL          0
      2000          1 Americas                        2600 LOWER LEVEL            0
      2000          1 Europe                         23900 LOWER LEVEL            0
      2000          1                                26500 LOWER LEVEL            0
      2000          2 Americas                        5000 LOWER LEVEL            0
      2000          2 Europe                          6800 LOWER LEVEL            0
      2000          2                                11800 LOWER LEVEL            0
      2000          3 Americas                        2200 LOWER LEVEL            0
      2000          3 Europe                          6400 LOWER LEVEL            0
      2000          3                                 8600 LOWER LEVEL            0
      2000          4 Europe                         12300 LOWER LEVEL            0
      2000          4                                12300 LOWER LEVEL            0
      2000                                           59200 TOP - 1 LEVEL          0
                                                    141100 TOP LEVEL              0
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now