Oracle Grouping ID, Grouping Sets
The example shows the use of GROUPING_ID with ROLLUP and GROUPING SETS to compute summary totals at intermediate levels, useful in data analysis and reporting. The count of phone lines in four area codes in two states are analyzed as shown below. With the GROUPING_ID, the highest ID represent the sum of all data as shown in Figure 1.

Use of GROUPING_ID with ROLLUP
SELECT DECODE(GROUPING_ID(q_tab.state_cd,q_tab.ph_cd),
0, ' Area Code '||TO_CHAR(q_tab.ph_cd),
1, ' '||q_tab.state_cd||' State Phone Count -> ',
'Total ('||TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss')||')') phone_code_details,
SUM(line_cnt) line_count
FROM
(SELECT 'PA' state_cd, 215 ph_cd, 395890 line_cnt FROM DUAL UNION
SELECT 'PA' state_cd, 610 ph_cd, 257980 line_cnt FROM DUAL UNION
SELECT 'PA' state_cd, 267 ph_cd, 375510 line_cnt FROM DUAL UNION
SELECT 'PA' state_cd, 484 ph_cd, 235900 line_cnt FROM DUAL UNION
SELECT 'NJ' state_cd, 609 ph_cd, 215490 line_cnt FROM DUAL UNION
SELECT 'NJ' state_cd, 918 ph_cd, 259100 line_cnt FROM DUAL UNION
SELECT 'NJ' state_cd, 856 ph_cd, 135900 line_cnt FROM DUAL UNION
SELECT 'NJ' state_cd, 201 ph_cd, 345290 line_cnt FROM DUAL) q_tab
GROUP BY ROLLUP(q_tab.state_cd,q_tab.ph_cd);

Figure 1

Use of GROUPING SETS
Similar result as shown by the use of GROUPING_ID with ROLLUP can be obtained by GROUPING SETS as shown in Figure 2.
 
SELECT DECODE(GROUPING(q_tab.ph_cd),
0, ' Area Code '||TO_CHAR(q_tab.ph_cd),
1, ' '||q_tab.state_cd||' State Phone Count -> ') phone_code_details,
SUM(line_cnt) line_count
FROM
(SELECT 'PA' state_cd, 215 ph_cd, 395890 line_cnt FROM DUAL UNION
SELECT 'PA' state_cd, 610 ph_cd, 257980 line_cnt FROM DUAL UNION
SELECT 'PA' state_cd, 267 ph_cd, 375510 line_cnt FROM DUAL UNION
SELECT 'PA' state_cd, 484 ph_cd, 235900 line_cnt FROM DUAL UNION
SELECT 'NJ' state_cd, 609 ph_cd, 215490 line_cnt FROM DUAL UNION
SELECT 'NJ' state_cd, 918 ph_cd, 259100 line_cnt FROM DUAL UNION
SELECT 'NJ' state_cd, 856 ph_cd, 135900 line_cnt FROM DUAL UNION
SELECT 'NJ' state_cd, 201 ph_cd, 345290 line_cnt FROM DUAL) q_tab
GROUP BY GROUPING SETS((q_tab.ph_cd,q_tab.state_cd), (q_tab.state_cd));

Figure 2


Back

Oracle registered trademark of Oracle Corporation.
Last Revised On: 09/29/2013

  1260