Solved

usage of grouping_id rollup

Posted on 2007-04-05
2
506 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

733 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