Why when using grouping_id with rollup the total will be level 3 instead of 2?

Question

Is there any logic behind the reason to why the total grouping when using rollup will be "lvl" 3?..

example:

col1	col2
id1	1
id1	2
id2	1
id2	2
id3	1
id3	2

In cube method this is understandable as

• level 0 is the "basic values" (my own term)such as "col1-ID1","col2-id1", "col3-id1" etc..,

• level 1 will be the subtotal row of each basic value which means subtotal of ID1(1)+ID1(2) --> id1(3) for example.

• level 2 will be the total of each combination which means subtotal of 1's and subtotal of 2's , in this example: 1- subtotal will be 3 and 2-subtotal will be 6

• and level 3 will be the grand total of them all, in this example: 9

my explanation might not make any sense:) .. sorry

Is there any reason behind that skipping from lvl 0/1 straight to 3? or its just the way it is?

Answer 1

Summary: The grand total will be in the grouping set with the id the maximum value of the grouping set which is equal to 2^{(number of columns in the grouping set)} - 1. Therefore, with 2 columns being cubed, the grand total is in 2²-1 = 3 and, with 3 columns being cubed, the grand total is in 2³-1 = 7.

---

Given the query:

SELECT LISTAGG(col1, ',') WITHIN GROUP (ORDER BY col1) AS col1,
       LISTAGG(col2, ',') WITHIN GROUP (ORDER BY col2) AS col2,
       GROUPING_ID(col1, col2) AS grp
FROM   table_name
GROUP BY CUBE(col1, col2)
ORDER BY grp, col1, col2

Which, for the sample data:

CREATE TABLE table_name (col1, col2) AS
SELECT 'id1', 1 FROM DUAL UNION ALL
SELECT 'id1', 2 FROM DUAL UNION ALL
SELECT 'id2', 1 FROM DUAL UNION ALL
SELECT 'id2', 2 FROM DUAL UNION ALL
SELECT 'id3', 1 FROM DUAL UNION ALL
SELECT 'id3', 2 FROM DUAL;

Outputs:

COL1	COL2	GRP
id1	1	0
id1	2	0
id2	1	0
id2	2	0
id3	1	0
id3	2	0
id1,id1	1,2	1
id2,id2	1,2	1
id3,id3	1,2	1
id1,id2,id3	1,1,1	2
id1,id2,id3	2,2,2	2
id1,id1,id2,id2,id3,id3	1,1,1,2,2,2	3

You can see that when GROUPING_ID(col1, col2) is:

0. The un-grouped value.
1. The value grouped by the first column in the grouping set (and there is one value for the first column and N values for the second column).
2. The value grouped by the second column in the grouping set (and there are M values for the first column and one value for the second column).
3. The value grouped by the both columns in the grouping set (and there are M values for the first column and N values for the second column giving N*M total values); which will give you the grand total.

---

If you had the sample data with 3 columns:

CREATE TABLE table2 (col1, col2, col3) AS
SELECT t1.COLUMN_VALUE,
       t2.COLUMN_VALUE,
       t3.COLUMN_VALUE
FROM   TABLE(SYS.ODCIVARCHAR2LIST('id1', 'id2', 'id3')) t1
       CROSS JOIN TABLE(SYS.ODCINUMBERLIST(1, 2)) t2
       CROSS JOIN TABLE(SYS.ODCINUMBERLIST(3, 4)) t3;

Then using CUBE across 3 columns:

SELECT LISTAGG(col1, ',') WITHIN GROUP (ORDER BY col1) AS col1,
       LISTAGG(col2, ',') WITHIN GROUP (ORDER BY col2) AS col2,
       LISTAGG(col3, ',') WITHIN GROUP (ORDER BY col3) AS col3,
       GROUPING_ID(col1, col2, col3) AS grp
FROM   table2
GROUP BY CUBE(col1, col2, col3)
ORDER BY grp, col1, col2, col3

Outputs:

COL1	COL2	COL3	GRP
id1	1	3	0
id1	1	4	0
id1	2	3	0
id1	2	4	0
id2	1	3	0
id2	1	4	0
id2	2	3	0
id2	2	4	0
id3	1	3	0
id3	1	4	0
id3	2	3	0
id3	2	4	0
id1,id1	1,1	3,4	1
id1,id1	2,2	3,4	1
id2,id2	1,1	3,4	1
id2,id2	2,2	3,4	1
id3,id3	1,1	3,4	1
id3,id3	2,2	3,4	1
id1,id1	1,2	3,3	2
id1,id1	1,2	4,4	2
id2,id2	1,2	3,3	2
id2,id2	1,2	4,4	2
id3,id3	1,2	3,3	2
id3,id3	1,2	4,4	2
id1,id1,id1,id1	1,1,2,2	3,3,4,4	3
id2,id2,id2,id2	1,1,2,2	3,3,4,4	3
id3,id3,id3,id3	1,1,2,2	3,3,4,4	3
id1,id2,id3	1,1,1	3,3,3	4
id1,id2,id3	1,1,1	4,4,4	4
id1,id2,id3	2,2,2	3,3,3	4
id1,id2,id3	2,2,2	4,4,4	4
id1,id1,id2,id2,id3,id3	1,1,1,1,1,1	3,3,3,4,4,4	5
id1,id1,id2,id2,id3,id3	2,2,2,2,2,2	3,3,3,4,4,4	5
id1,id1,id2,id2,id3,id3	1,1,1,2,2,2	3,3,3,3,3,3	6
id1,id1,id2,id2,id3,id3	1,1,1,2,2,2	4,4,4,4,4,4	6
id1,id1,id1,id1,id2,id2,id2,id2,id3,id3,id3,id3	1,1,1,1,1,1,2,2,2,2,2,2	3,3,3,3,3,3,4,4,4,4,4,4	7

And will generate 2³ = 8 levels (from 0 to 7) since there are all the possible combinations of grouping 3 columns and the grand-total will be in level 7; compared to 2² levels (0 to 3) when you are cubing 2 columns and the grand total is in level 3.

fiddle

---

Update

What I don't understand is why roll up is skipping level 2 straight to 3?

From the SELECT documentation:

ROLLUP

The ROLLUP operation in the simple_grouping_clause groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions in the GROUP BY specification, and returns a single row of summary for each group.

[...]

CUBE

The CUBE operation in the simple_grouping_clause groups the selected rows based on the values of all possible combinations of expressions in the specification. It returns a single row of summary information for each group.

CUBE generates all possible grouping sets; ROLLUP generates groups of the first 1 column then with the first 2 columns, 3 columns, ..., up to n columns which is the same as the CUBE when the grouping sets are restricted to the 2⁰-1, 2¹-1, 2²-1, ..., 2ⁿ-1 (or more simply 0, 1, 3, 7, ... 2ⁿ-1).

This means that ROLLUP will skip the grouping set with id 2 as that is grouping only by the 2nd column and that is not "one of the first n, n-1, n-2, ... 0 expressions" in the GROUP BY specification.

fiddle