Roll Up operations in Oracle 10g XE

The purpose of this post is to let you guys know the basic OLAP rollup operation using Oracle 10g XE which is a free DBMS


First to begin with

Rollup is formally defined as an OLAP operation involving computing all of the data relationships for one or more dimensions. To do this, a computational relationship or formula might be defined.

I guess that didnt help even a bit. :p


To make things simpler


I would say a rollup  creates subtotals(totalling is an example relation considered in this case) that roll up from the most detailed level to a grand total, following a grouping list specified in the ROLLUP clause. 


ROLLUP takes as its argument an ordered list of grouping columns.


First, it calculates the standard aggregate values specified in the GROUP BY clause.


Then, it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. Finally, it creates a grand total.


To explain this consider the following example



create table Prac (
pid int primary key,
name varchar2(10),
country varchar2(15),
cost int
);


--------------------------------------------


After Filling in data


---------------------------------------------

PID	NAME	COUNTRY	COST
1	Rahul	IN	1000
2	Rohit	IN	1000
3	Rahul	US	1500
4	Rohit	US	1500
5	Amit	US	2000

----------------------------------------------


Then i execute a simple GROUP BY query on SUM(Cost) Group By Country


select country,sum(cost) from prac group by country

COUNTRY	SUM(COST)
US	5000
IN	2000

I get the above result i.e sum of cost for each country.... this is simple




------------------------------------------------


Now a Rollup is an extension of Group By Clause


Consider the following 


select country,sum(cost) from prac group by rollup(country)

COUNTRY	SUM(COST)
IN	2000
US	5000
-	7000

Now the only difference is apart from producing a country-wise sum of cost.. the query also rolled them up to produce their total i.e 7000


-----------------------------------------------------


Now Consider another example
select name,country,sum(cost) from prac group by rollup(name,country)

NAME	COUNTRY	SUM(COST)
Amit	US	2000
Amit	-	2000
Rahul	IN	1000
Rahul	US	1500
Rahul	-	2500
Rohit	IN	1000
Rohit	US	1500
Rohit	-	2500
-	-	7000

Here we see that we get Sum of cost for every Name,Country pair
Additionally we also get some more sub-totals.


Now at level 1 we get Sum(Cost) of every Name-Country pair now grouped by Name(since name is written before Country) for every pair.. hence arguements passed to rollup operation is an ordered list i.e order matters here .


And at level 2 further we get Sum(Cost) of every subtotals generated at level 1 now grouped by nothing i.e the entire total.


-----------------------------------------------------------
To explain the importance of ordering of parameters in rollup we consider yet another example


select name,country,sum(cost) from prac group by rollup(country,name)


Corresponding result set generated is-

NAME	COUNTRY	SUM(COST)
Rahul	IN	1000
Rohit	IN	1000
-	IN	2000
Amit	US	2000
Rahul	US	1500
Rohit	US	1500
-	US	5000
-	-	7000

Here as we observe, at level 1 we get Sum(Cost) of every Name-Country pair now grouped by Country(since country is written before Name) for every pair


Again at level 2 further we get Sum(Cost) of every subtotals generated at level 1 now grouped by nothing i.e the entire total.




-------------------------------------------




Now we read the definition yet again..
Rollup is formally defined as an OLAP operation involving computing all of the data relationships for one or more dimensions. 

 "computing all of the data relationships for one or more dimensions"
Means
Like here computing subtotals at every level grouping one or more columns(dimensions)