Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

ROLLUP, CUBE, GROUPING Functions and GROUPING SETS - An overview of some functionality available for aggregation in data warehouses.



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

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!

Add your comments here.
Name
Comment
(max 400 chars - plain text)