The query shown below adds another data element - fiscal year to be grouped along with quarter and region in the summary query. Using GROUPING_ID, it can be analyzed as shown below.
Region/Quarter/Year Query 
SELECT  qt_sum.f_year, qt_sum.f_qtr, qt_sum.region_cd,
 GROUPING_ID(qt_sum.f_year)|| GROUPING_ID(qt_sum.f_qtr)|| GROUPING_ID(qt_sum.region_cd) bit_vector,
 GROUPING_ID(qt_sum.f_year,qt_sum.f_qtr, qt_sum.region_cd) grouping_id,
 SUM(qt_sum.sales) sales
FROM
 (SELECT 2005 f_year, '1Q' f_qtr, 'North' region_cd,  17050 sales FROM DUAL UNION
  SELECT 2005 f_year, '2Q' f_qtr, 'North' region_cd,  18750 sales FROM DUAL UNION
  SELECT 2005 f_year, '3Q' f_qtr, 'North' region_cd,  21150 sales FROM DUAL UNION
  SELECT 2005 f_year, '4Q' f_qtr, 'North' region_cd,  15125 sales FROM DUAL UNION
  SELECT 2006 f_year, '1Q' f_qtr, 'North' region_cd,  18950 sales FROM DUAL UNION
  SELECT 2006 f_year, '2Q' f_qtr, 'North' region_cd,  17450 sales FROM DUAL UNION
  SELECT 2006 f_year, '3Q' f_qtr, 'North' region_cd,  23925 sales FROM DUAL UNION
  SELECT 2006 f_year, '4Q' f_qtr, 'North' region_cd,  17925 sales FROM DUAL UNION
  SELECT 2005 f_year, '1Q' f_qtr, 'South' region_cd,  14550 sales FROM DUAL UNION
  SELECT 2005 f_year, '2Q' f_qtr, 'South' region_cd,  19725 sales FROM DUAL UNION
  SELECT 2005 f_year, '3Q' f_qtr, 'South' region_cd,  18850 sales FROM DUAL UNION
  SELECT 2005 f_year, '4Q' f_qtr, 'South' region_cd,  11125 sales FROM DUAL UNION
  SELECT 2006 f_year, '1Q' f_qtr, 'South' region_cd,  17575 sales FROM DUAL UNION
  SELECT 2006 f_year, '2Q' f_qtr, 'South' region_cd,  23225 sales FROM DUAL UNION
  SELECT 2006 f_year, '3Q' f_qtr, 'South' region_cd,  16350 sales FROM DUAL UNION
  SELECT 2006 f_year, '4Q' f_qtr, 'South' region_cd,  14925 sales FROM DUAL)  qt_sum
GROUP BY ROLLUP(qt_sum.f_year, qt_sum.f_qtr, qt_sum.region_cd);
Above Query -> Output

Region/Quarter/Year In Report Format 
SELECT  DECODE(Grouping_ID(qt_sum.f_year, qt_sum.f_qtr, qt_sum.region_cd),
  0, qt_sum.region_cd,
  1, '    Total ['||qt_sum.f_qtr||'] ....',
  3, '  '||TO_CHAR(qt_sum.f_year)||' Annual Total ....',
     'Grand Total ......... ')    region_sales_by_quarter_year,
 GROUPING_ID(qt_sum.f_year)|| GROUPING_ID(qt_sum.f_qtr)|| GROUPING_ID(qt_sum.region_cd) bit_vector,
 SUM(qt_sum.sales) sales
FROM
 (SELECT 2005 f_year, '1Q' f_qtr, 'North' region_cd,  17050 sales FROM DUAL UNION
  SELECT 2005 f_year, '2Q' f_qtr, 'North' region_cd,  18750 sales FROM DUAL UNION
  SELECT 2005 f_year, '3Q' f_qtr, 'North' region_cd,  21150 sales FROM DUAL UNION
  SELECT 2005 f_year, '4Q' f_qtr, 'North' region_cd,  15125 sales FROM DUAL UNION
  SELECT 2006 f_year, '1Q' f_qtr, 'North' region_cd,  18950 sales FROM DUAL UNION
  SELECT 2006 f_year, '2Q' f_qtr, 'North' region_cd,  17450 sales FROM DUAL UNION
  SELECT 2006 f_year, '3Q' f_qtr, 'North' region_cd,  23925 sales FROM DUAL UNION
  SELECT 2006 f_year, '4Q' f_qtr, 'North' region_cd,  17925 sales FROM DUAL UNION
  SELECT 2005 f_year, '1Q' f_qtr, 'South' region_cd,  14550 sales FROM DUAL UNION
  SELECT 2005 f_year, '2Q' f_qtr, 'South' region_cd,  19725 sales FROM DUAL UNION
  SELECT 2005 f_year, '3Q' f_qtr, 'South' region_cd,  18850 sales FROM DUAL UNION
  SELECT 2005 f_year, '4Q' f_qtr, 'South' region_cd,  11125 sales FROM DUAL UNION
  SELECT 2006 f_year, '1Q' f_qtr, 'South' region_cd,  17575 sales FROM DUAL UNION
  SELECT 2006 f_year, '2Q' f_qtr, 'South' region_cd,  23225 sales FROM DUAL UNION
  SELECT 2006 f_year, '3Q' f_qtr, 'South' region_cd,  16350 sales FROM DUAL UNION
  SELECT 2006 f_year, '4Q' f_qtr, 'South' region_cd,  14925 sales FROM DUAL)  qt_sum
GROUP BY ROLLUP(qt_sum.f_year, qt_sum.f_qtr, qt_sum.region_cd);
Above Query -> Output

Highest Value of GROUPING_ID in Summary Query with ROLLUP/CUBE
Grouped Columns Grouping_ID Bit Vector Computed Value 2Grouped Columns - 1
1 1 1 20 = 1 21 - 1 = 1
2 3 11 20 + 21 = 3 22 - 1 = 3
3 7 111 20 + 21 + 22 = 7 23 - 1 = 7
4 15 1111 20 + 21 + 22 + 23 = 15 24 - 1 = 15
5 31 11111 20 + 21 + 22 + 23 + 24 = 31 25 - 1 = 31
N 2N - 1 NNNN.... 20 + 21 + 22 + 23 + 24 = ... 2N - 1 = ...

Oracle registered trademark of Oracle Corporation.
Last Revised on: February 23, 2012

  74118