|
|
|
|
This is a way to analyze grouped data when grouping more than one column with GROUP BY ROLLUP(col1,col2,..) using GROUPING_ID(col1,col2,..).
The query below shows how to use the GROUP BY function with ROLLUP for grouping single column using GROUPING() function in summary queries as shown in example 1. This is a feature of Oracle 8i and higher.
Example 1:
SELECT
SUBSTR(DECODE(GROUPING(month_id),
0,TO_CHAR(month_id),
'Total ('||TO_CHAR(SYSDATE)||'): '),1,20) Details,
COUNT(*) AllData,
ROUND(SUM(balance_due),0) balance_due
FROM monthly_ar_detail
GROUP BY ROLLUP(month_id);
DETAILS ALLDATA BALANCE_DUE
-------------------- ---------- -----------
200410 45 20284
200411 51 20171
200412 60 25977
Total (28-DEC-04): 156 66432
Example 1a:
The same can be done in Oracle 9i and higher using GROUPING_ID() as shown below (essentially same result as in example 1):
SELECT
SUBSTR(DECODE(GROUPING_ID(month_id),
0,TO_CHAR(month_id),
'Total ('||TO_CHAR(SYSDATE)||'): '),1,20) Details,
COUNT(*) AllData,
GROUPING_ID(month_id) grouping_id,
ROUND(SUM(balance_due),0) balance_due
FROM monthly_ar_detail
GROUP BY ROLLUP(month_id);
DETAILS ALLDATA GROUPING_ID BALANCE_DUE
-------------------- ---------- ----------- -----------
200410 45 0 20284
200411 51 0 20171
200412 60 0 25977
Total (28-DEC-04): 156 1 66432
The query shown in Example 2 (below) shows how to use GROUPING_ID function with ROLLUP for grouping more than one column and to get subtotals for different grouped columns (rollup values). GROUPING_ID is a feature in Oracle 9i and higher. GROUPING_ID returns a number corresponding to the GROUPING bit vector associated with a row (per Oracle documentation). This can also be used in select statements that use GROUP BY clause with CUBE also.
Mathematically the maximum value of GROUPING_ID(col1,col2,.. etc) can be derived as:
Grouping_ID(n) = 2 * Grouping_ID(n-1) + 1
where the first term is Grouping_ID(1) = 0
For grouping with one column the value of n is equal to number of grouped columns + 1
i.e.
For the query
SELECT col1, GROUPING_ID(col1), SUM(col1) FROM any_table GROUP BY ROLLUP(col1)
the value of n is 2 since it is a single column group by clause. To get the max. value of GROUPING_ID using the function
Grouping_ID(n) = 2 * Grouping_ID(n-1) + 1 Grouping_ID(2) = 2 * Grouping_ID(1) + 1 = 2 * 0 + 1 = 1 Maximum value of Grouping_ID = 1
For GROUP BY ROLLUP(col1,col2) the value of n = 3 for the function
Grouping_ID(n) = 2 * Grouping_ID(n-1) + 1 Grouping_ID(3) = 2 * Grouping_ID(2) + 1 = 2 * 1 + 1 = 3 Maximum value of Grouping_ID = 3
This is as shown in the example query below (Example 2).
GROUP BY ROLLUP(col1,col2,col3) the value of n = 4 Maximum value of Grouping_ID = 7 Grouping_ID(4) = 2 * Grouping_ID(3) + 1 = 7 GROUP BY ROLLUP(col1,col2,col3,col4) the value of n = 5 Maximum value of Grouping_ID = 15 Grouping_ID(5) = 2 * Grouping_ID(4) + 1 = 15
Thus the maximum value of GROUPING_ID is a numeric series of 1, 3, 7, 15, 31, 63, 127, 255, 511, .... where each number represents maximum value of GROUPING_ID of a query that is summarized for single, two column, three column, ... respectively with a GROUP BY ROLLUP(col1, col2) etc. The maximum value of GROUPING_ID corresponds to rollup value or total of all the grouped columns.
The subtotals and total can be analyzed at various levels by knowing the different GROUPING_IDs. Also subtotals/summary levels can be skipped where not necessary by using a HAVING GROUPING_ID(col1,col2,..) <> 3, 7 etc. as shown in example 3.
NOTE: When using HAVING(col1,col2,..) the totals represent the output that satisfy the WHERE clause and so the total will not match what is selected/filtered by the HAVING(col1,col2,...) clause.
The example below shows a query that uses two column GROUP BY ROLLUP(col1,col2).
Example 2a:
SELECT
SUBSTR(DECODE(GROUPING_ID(aging_bucket_id, month_id),
0,' '||TO_CHAR(month_id),
1,' Total ('||
DECODE(aging_bucket_id,
1,' Current ',
2,' 0 - 30 Days',
3,' 31 - 60 Days',
4,' 61 - 90 Days',
5,' 91 - 120 Days',
' Over 120 Days')||') - ',
3, 'Total ('||TO_CHAR(SYSDATE)||'): '),1,25)
Details,
COUNT(*) Rollup_count,
GROUPING_ID(aging_bucket_id,month_id) grouping_id,
ROUND(SUM(balance_due),0) SUM_balance_$$
FROM monthly_ar_detail
GROUP BY ROLLUP(aging_bucket_id,month_id)
;
DETAILS ROLLUP_COUNT GROUPING_ID SUM_BALANCE_$$
------------------------- ------------ ----------- --------------
200410 22 0 16982
200411 24 0 15551
200412 30 0 19265
Total ( Current ) - 76 1 51798
200410 4 0 1251
200411 4 0 1318
200412 6 0 2438
Total ( 0 - 30 Days) - 14 1 5007
200410 2 0 113
200411 5 0 1305
200412 3 0 950
Total ( 31 - 60 Days) - 10 1 2368
200411 1 0 60
200412 5 0 1305
Total ( 61 - 90 Days) - 6 1 1364
200410 3 0 1926
200412 1 0 60
Total ( 91 - 120 Days) - 4 1 1986
200410 14 0 11
200411 17 0 1937
200412 15 0 1960
Total ( Over 120 Days) - 46 1 3908
Total (28-DEC-04): 156 3 66432
Example 2b:
The summary with rollup can changed by changing (swapping) the order of the columns as shown below.
SELECT
SUBSTR(DECODE(GROUPING_ID(month_id, aging_bucket_id),
0, DECODE(aging_bucket_id,
1,' Current ',
2,' 0 - 30 Days',
3,' 31 - 60 Days',
4,' 61 - 90 Days',
5,' 91 - 120 Days',
' Over 120 Days'),
1, 'Total For '||TO_CHAR(month_id),
3, 'Total ('||TO_CHAR(SYSDATE)||'): '),1,25)
Details,
COUNT(*) AllData,
GROUPING_ID(month_id, aging_bucket_id) grouping_id,
ROUND(SUM(balance_due),0) balance_due
FROM monthly_ar_detail
GROUP BY ROLLUP(month_id, aging_bucket_id)
;
DETAILS ALLDATA GROUPING_ID BALANCE_DUE
------------------------- ---------- ----------- -----------
Current 22 0 16982
0 - 30 Days 4 0 1251
31 - 60 Days 2 0 113
91 - 120 Days 3 0 1926
Over 120 Days 14 0 11
Total For 200410 45 1 20284
Current 24 0 15551
0 - 30 Days 4 0 1318
31 - 60 Days 5 0 1305
61 - 90 Days 1 0 60
Over 120 Days 17 0 1937
Total For 200411 51 1 20171
Current 30 0 19265
0 - 30 Days 6 0 2438
31 - 60 Days 3 0 950
61 - 90 Days 5 0 1305
91 - 120 Days 1 0 60
Over 120 Days 15 0 1960
Total For 200412 60 1 25977
Total (28-DEC-04): 156 3 66432
The example below shows a query that uses three column GROUP BY ROLLUP(col1,col2,col3).
Example 3: Excludes GROUPING_ID 3 (max. GROUPING_ID is 7)
SELECT
SUBSTR(DECODE(GROUPING_ID(aging_bucket_id, month_id, customer_cd),
0, 'Customer No. '||customer_cd,
1, TO_CHAR(month_id)||' Total ('||
DECODE(aging_bucket_id,1,' Current ',
2,' 0 - 30 Days',
3,' 31 - 60 Days',
4,' 61 - 90 Days',
5,' 91 - 120 Days',
' Over 120 Days')||'): ',
7, 'Total Balance ('||TO_CHAR(SYSDATE)||'): '),1,30)
Details,
COUNT(*) AllData,
GROUPING_ID(aging_bucket_id, month_id, customer_cd) grouping_id,
ROUND(SUM(balance_due),0) balance_due
FROM monthly_ar_detail
WHERE customer_cd IN (1000, 2000)
GROUP BY ROLLUP(aging_bucket_id, month_id, customer_cd)
HAVING GROUPING_ID(aging_bucket_id, month_id, customer_cd) <> 3 ;
DETAILS ALLDATA GROUPING_ID BALANCE_DUE
------------------------------ ---------- ----------- -----------
Customer No. 1000 22 0 16982
Customer No. 2000 47 0 30430
200410 Total ( Current ): 69 1 47412
Customer No. 1000 24 0 15551
Customer No. 2000 25 0 17281
200411 Total ( Current ): 49 1 32832
Customer No. 1000 30 0 19265
Customer No. 2000 31 0 16904
200412 Total ( Current ): 61 1 36169
Customer No. 1000 4 0 1251
Customer No. 2000 17 0 10190
200410 Total ( 0 - 30 Days): 21 1 11442
Customer No. 1000 4 0 1318
Customer No. 2000 5 0 5115
200411 Total ( 0 - 30 Days): 9 1 6433
Customer No. 1000 6 0 2438
Customer No. 2000 8 0 2152
200412 Total ( 0 - 30 Days): 14 1 4590
Customer No. 1000 2 0 113
Customer No. 2000 12 0 7502
200410 Total ( 31 - 60 Days): 14 1 7615
Customer No. 1000 5 0 1305
Customer No. 2000 2 0 194
200411 Total ( 31 - 60 Days): 7 1 1499
Customer No. 1000 3 0 950
Customer No. 2000 3 0 1273
200412 Total ( 31 - 60 Days): 6 1 2223
Customer No. 2000 4 0 3512
200410 Total ( 61 - 90 Days): 4 1 3512
Customer No. 1000 1 0 60
Customer No. 2000 1 0 133
200411 Total ( 61 - 90 Days): 2 1 192
Customer No. 1000 5 0 1305
Customer No. 2000 2 0 194
200412 Total ( 61 - 90 Days): 7 1 1499
Customer No. 1000 3 0 1926
Customer No. 2000 1 0 -89
200410 Total ( 91 - 120 Days): 4 1 1838
Customer No. 2000 2 0 314
200411 Total ( 91 - 120 Days): 2 1 314
Customer No. 1000 1 0 60
Customer No. 2000 1 0 133
200412 Total ( 91 - 120 Days): 2 1 192
Customer No. 1000 14 0 11
Customer No. 2000 11 0 -4190
200410 Total ( Over 120 Days): 25 1 -4179
Customer No. 1000 17 0 1937
Customer No. 2000 12 0 -4299
200411 Total ( Over 120 Days): 29 1 -2362
Customer No. 1000 15 0 1960
Customer No. 2000 14 0 -3985
200412 Total ( Over 120 Days): 29 1 -2025
Total Balance (28-DEC-04): 354 7 149197
Similarly totals at different rollup levels (GROUP BY with ROLLUP/CUBE) can be analyzed by excluding GROUPING_ID() for ones that are not necessary and knowing the maximum value of the GROUPING_ID() using formula (as shown in example 2 above). When ROLLUP() is excluded in the query, the result corresponding of zeroth value of the GROUPING_ID() is displayed.