Oracle Grouping

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,