Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

usage of grouping_id rollup

Posted on 2007-04-05
2
Medium Priority
?
509 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 2000 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 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

610 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