-- 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);
-- 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);
72925