8i | 9i | 10g | 11g | 12c | Misc | PL/SQL | SQL | RAC | Linux

Home » Misc » Here

Comments for ROLLUP, CUBE, GROUPING Functions and GROUPING SETS


lesio said...

Very good and precise explanation :)

DongHang said...

Thanks for details explanation.

Raghu said...

Hi Tim,
Thanks for the elaborated explanation on ROLLUP. However I don't understand your point
{quote}If "n" is the number of columns listed in the ROLLUP, there will be n+1 levels of subtotals.{quote}
How the levels of subtotals could only depend on "columns list inside rollup", I believe it also depends on the no.of distinct values in each of those columns?

Raghu said...

I wrote a small pseudo code that would return no.of “subtotals” given 'n' is number of columns inside rollup. Please correct me if I misread your point.
for x in 1..n-1 loop
temp := null;
for i in 1..x
loop
temp := (nvl(temp,1) * distinct(count(col(i))))
end loop;
subtotals := nvl(subtotals,0)+temp;
end loop;

Tim... said...

Hi.

"n+1 levels of subtotals". **levels** is the important word. This means "types" of subtotals, not the total number of each type of subtotals.

Cheers

Tim...

PS. Questions in FORUM!!!

AAA said...

explanation could have been more elaborate....very confusing for me :/

Tim... said...

Hi.

Like all my articles, it is intentionally brief. if you need more details, you should read the Oracle documentation linked to at the end of the article.

Cheers

Tim...

Max said...

Hi Tim,
I just read your explanation on the ROLLUP statement, quoting "If "n" is the number of columns listed in the ROLLUP, there will be n+1 levels of subtotals."

Your query has two columns inside the ROLLUP statement:

SELECT fact_1_id,
fact_2_id,
SUM(sales_value) AS sales_value
FROM dimension_tab
GROUP BY ROLLUP (fact_1_id, fact_2_id)
ORDER BY fact_1_id, fact_2_id;

Max said...

And in the results you only have 2 levels of ROLLUP, totals when fact_2_id is null and the big total when both ROLLUP columns are null.

Max said...

That, of course, if you don't count the SUM value for single values of columns 1 and 2 as a level. If you do count it as a level, then you do have n+1 levels.

Aside that, big help.

Tim... said...

Hi.

I do count that. :)

As do Oracle. See this paragraph in the documents.

http://docs.oracle.com/cd/E11882_01/server.112/e16579/aggreg.htm#i1007413

Cheers

Tim...

Ronan said...

Hi Tim. This article has helped me immensely and I have found myself referring to it time and again. thanks for sharing

Santosh said...

nice one..thanx for this article..
superb

Piotr A said...

Hi Tim,
Thanks for article.I think it is very clear.
One question - how about situation when n=1? Do we have n+1 levels of subtotal in output? If we have one field in rollup grouping, there is only one field with grand total.

Tim... said...

Hi.

Still n+1 levels of subtotals. 1 column. A subtotal for each value of that column and a grand total. That's two lots of totals. n=1, 1+1 = 2. :)

If in doubt, try it for yourself! :)

Cheers

Tim...

Piotr A said...

SELECT fact_1_id,
SUM(sales_value) AS sales_value
from DIMENSION_TAB
GROUP BY ROLLUP (fact_1_id)
ORDER BY fact_1_id;

1 25854,94 -- this is subtotal for column fact_1_id (n=1)
2 24895,58 -- this is subtotal for column fact_1_id (n=1)
50750,52 -- this is grand total (n=2)

OK. Now I know what n means :) Thanks for explanation.

Tim... said...

Hi.

Yep. :)

Cheers

Tim...

DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!