Solved

usage of grouping_id rollup

Posted on 2007-04-05
2
498 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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.

744 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

8 Experts available now in Live!

Get 1:1 Help Now