Solved

usage of grouping_id rollup

Posted on 2007-04-05
2
507 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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

695 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