GROUPING_ID - Summary Order Analysis

The query below shows the "Regional Sales Summary By Quarter" as specified in the ROLLUP(qt_sales.region_cd, qt_sales.f_qtr).

SELECT   qt_sales.region_cd, qt_sales.f_qtr quarter,
 GROUPING_ID(qt_sales.region_cd)||GROUPING_ID(qt_sales.f_qtr) bit_vector,
 GROUPING_ID(qt_sales.region_cd, qt_sales.f_qtr) grouping_id,
 SUM(qt_sales.sales) sales
FROM
 (SELECT '1Q' f_qtr, 'North' region_cd,  17050 sales FROM DUAL UNION
  SELECT '2Q' f_qtr, 'North' region_cd,  18750 sales FROM DUAL UNION
  SELECT '3Q' f_qtr, 'North' region_cd,  21150 sales FROM DUAL UNION
  SELECT '4Q' f_qtr, 'North' region_cd,  15125 sales FROM DUAL UNION
  SELECT '1Q' f_qtr, 'South' region_cd,  14550 sales FROM DUAL UNION
  SELECT '2Q' f_qtr, 'South' region_cd,  19725 sales FROM DUAL UNION
  SELECT '3Q' f_qtr, 'South' region_cd,  18850 sales FROM DUAL UNION
  SELECT '4Q' f_qtr, 'South' region_cd,  11125 sales FROM DUAL)  qt_sales
GROUP BY ROLLUP(qt_sales.region_cd, qt_sales.f_qtr);
-- Query Output

The above query can be changed to "Quarterly Sales Summary By Region" by changing the order of columns in the rollup as specified in the ROLLUP(qt_sales.f_qtr, qt_sales.region_cd) condition. The bit vector and grouping_id computation are based on the rollup condition as shown in output diagram.

SELECT   qt_sales.f_qtr quarter, qt_sales.region_cd,
 GROUPING_ID(qt_sales.f_qtr)||GROUPING_ID(qt_sales.region_cd) bit_vector,
 GROUPING_ID(qt_sales.f_qtr, qt_sales.region_cd) grouping_id,
 SUM(qt_sales.sales) sales
FROM
 (SELECT '1Q' f_qtr, 'North' region_cd,  17050 sales FROM DUAL UNION
  SELECT '2Q' f_qtr, 'North' region_cd,  18750 sales FROM DUAL UNION
  SELECT '3Q' f_qtr, 'North' region_cd,  21150 sales FROM DUAL UNION
  SELECT '4Q' f_qtr, 'North' region_cd,  15125 sales FROM DUAL UNION
  SELECT '1Q' f_qtr, 'South' region_cd,  14550 sales FROM DUAL UNION
  SELECT '2Q' f_qtr, 'South' region_cd,  19725 sales FROM DUAL UNION
  SELECT '3Q' f_qtr, 'South' region_cd,  18850 sales FROM DUAL UNION
  SELECT '4Q' f_qtr, 'South' region_cd,  11125 sales FROM DUAL)  qt_sales
GROUP BY ROLLUP(qt_sales.f_qtr, qt_sales.region_cd);
-- Query Output

By using DECODE() on grouping_id and using report specific columns for appropriate values of grouping_id (0,1,.. etc.), the query can be converted into a report as shown in query below.

Regional Sales Summary By Quarter (Report)

SELECT  DECODE(Grouping_ID(qt_sales.region_cd, qt_sales.f_qtr),
  0, qt_sales.f_qtr,
  1, ' Total For '||qt_sales.region_cd||' ....', 
     'Annual Total ')    quarterly_sales_by_region,
 GROUPING_ID(qt_sales.region_cd)||GROUPING_ID(qt_sales.f_qtr) bit_vector,
 GROUPING_ID(qt_sales.region_cd, qt_sales.f_qtr) grouping_id,
 SUM(qt_sales.sales) sales
FROM
 (SELECT '1Q' f_qtr, 'North' region_cd,  17050 sales FROM DUAL UNION
  SELECT '2Q' f_qtr, 'North' region_cd,  18750 sales FROM DUAL UNION
  SELECT '3Q' f_qtr, 'North' region_cd,  21150 sales FROM DUAL UNION
  SELECT '4Q' f_qtr, 'North' region_cd,  15125 sales FROM DUAL UNION
  SELECT '1Q' f_qtr, 'South' region_cd,  14550 sales FROM DUAL UNION
  SELECT '2Q' f_qtr, 'South' region_cd,  19725 sales FROM DUAL UNION
  SELECT '3Q' f_qtr, 'South' region_cd,  18850 sales FROM DUAL UNION
  SELECT '4Q' f_qtr, 'South' region_cd,  11125 sales FROM DUAL)  qt_sales
GROUP BY ROLLUP(qt_sales.region_cd, qt_sales.f_qtr);

Regional Sales Summary By Quarter (Report)

Quarterly Sales Summary By Region (Report)

SELECT  DECODE(Grouping_ID(qt_sales.f_qtr, qt_sales.region_cd),
  0, qt_sales.region_cd,
  1, ' Total For '||qt_sales.f_qtr||' ....',  
     'Annual Total ')    region_sales_by_quarter,
 GROUPING_ID(qt_sales.f_qtr)||GROUPING_ID(qt_sales.region_cd) bit_vector,
 GROUPING_ID(qt_sales.f_qtr, qt_sales.region_cd) grouping_id,
 SUM(qt_sales.sales) sales
FROM
 (SELECT '1Q' f_qtr, 'North' region_cd,  17050 sales FROM DUAL UNION
  SELECT '2Q' f_qtr, 'North' region_cd,  18750 sales FROM DUAL UNION
  SELECT '3Q' f_qtr, 'North' region_cd,  21150 sales FROM DUAL UNION
  SELECT '4Q' f_qtr, 'North' region_cd,  15125 sales FROM DUAL UNION
  SELECT '1Q' f_qtr, 'South' region_cd,  14550 sales FROM DUAL UNION
  SELECT '2Q' f_qtr, 'South' region_cd,  19725 sales FROM DUAL UNION
  SELECT '3Q' f_qtr, 'South' region_cd,  18850 sales FROM DUAL UNION
  SELECT '4Q' f_qtr, 'South' region_cd,  11125 sales FROM DUAL)  qt_sales
GROUP BY ROLLUP(qt_sales.f_qtr, qt_sales.region_cd);

Quarterly Sales Summary By Region (Report)

Using similar GROUPING_ID and ROLLUP logic, reports can be easily created for several summary levels. It is essential to know the increasing value of GROUPING_ID with more columns used in the group by clause. The maximum value (bit vector) of Grouping_ID can be computed as 2ⁿ-1, where n is the number of columns that are being grouped in the query (in the above example it is 2²-1 = 3 and for 3 columns it is 7 and so on).

Three Column GROUPING_ID - Analysis

Oracle registered trademark of Oracle Corporation.

Last Revised on: February 23, 2012

73876