Rollup Query - Grouping_ID (Four Column)
-- In the query below the data is grouped on four columns with 
-- maximum value of grouping_id = 15 (bit vector -> 1111)
-- computed as 24 - 1 = 15
-- This represents the sum of all sales in the query table
-- 100 + 201 + 202 + 203 + 204 = 910

WITH qt AS
(SELECT 1 idx, 11 div, 101 prod, 1001 cid, 100 sale FROM DUAL UNION
 SELECT 2 idx, 21 div, 211 prod, 2001 cid, 201 sale FROM DUAL UNION
 SELECT 2 idx, 22 div, 221 prod, 2002 cid, 202 sale FROM DUAL UNION
 SELECT 2 idx, 22 div, 222 prod, 2003 cid, 203 sale FROM DUAL UNION
 SELECT 2 idx, 22 div, 222 prod, 2004 cid, 204 sale FROM DUAL)
SELECT 
 qt.idx, qt.div, qt.prod, qt.cid,
 CAST(GROUPING_ID(qt.idx)||GROUPING_ID(qt.div)||
 GROUPING_ID(qt.prod)||GROUPING_ID(qt.cid) AS
 VARCHAR2(10)) bit_vector,
 GROUPING_ID(qt.idx, qt.div, qt.prod, qt.cid) grouping_id,
 SUM(sale) sales
FROM qt
GROUP BY ROLLUP(qt.idx, qt.div, qt.prod, qt.cid);
Rollup Query - Grouping_ID (Four Column) Analysis


Rollup Query - Grouping_ID (Four Column) Analysis
-- The query below shows the analysis of column based (data) summary using 
-- Grouping_ID and to get grand total of data element such as sales
WITH qt AS
(SELECT 1 idx, 11 div, 101 prod, 1001 cid, 100 sale FROM DUAL UNION
 SELECT 2 idx, 21 div, 211 prod, 2001 cid, 201 sale FROM DUAL UNION
 SELECT 2 idx, 22 div, 221 prod, 2002 cid, 202 sale FROM DUAL UNION
 SELECT 2 idx, 22 div, 222 prod, 2003 cid, 203 sale FROM DUAL UNION
 SELECT 2 idx, 22 div, 222 prod, 2004 cid, 204 sale FROM DUAL)
SELECT  -- qt.idx, qt.div, qt.prod, qt.cid,
 GROUPING_ID(qt.idx)||
 GROUPING_ID(qt.div)||
 GROUPING_ID(qt.prod)||
 GROUPING_ID(qt.cid)  bit_vector,
 DECODE(GROUPING_ID(qt.idx, qt.div, qt.prod, qt.cid),
  0, 'SUM By idx,div,prod,cid -> ('||
       qt.idx||','||qt.div||','||qt.prod||','||qt.cid||')',
  1, 'SUM By idx,div,prod     -> ('||qt.idx||','||qt.div||','||qt.prod||')',
  3, 'SUM By idx,div          -> ('||qt.idx||','||qt.div||')',
  7, 'SUM By idx              -> ('||qt.idx||')',
  'Total ....              ->  ')  grouping_sum,
 SUM(sale) sales
FROM qt
GROUP BY ROLLUP(qt.idx, qt.div, qt.prod, qt.cid);
Rollup Query - Grouping_ID (Four Column) Summary Analysis


Three Column Grouping/Rollup Grouping_ID

Oracle registered trademark of Oracle Corporation.

Last Revised On: Sptember 01, 2014

  1315