Oracle ROLLUP Usage- Example

For some time till now, ROLLUP and CUBE were always complicated terms to comprehend and use in standard SQL usage for me. Today I decided to give it a go and understand them with some examples. So far so good and the simulation for ROLLUP is provided below. Let us create a basic table and do some updates so that we can get going.

CREATE TABLE SAM_STR AS
SELECT TRUNC(DBMS_RANDOM.value(1000,9999)) AS VAL FROM ALL_OBJECTS WHERE ROWNUM < 1001

Here we create a basic table SAM_STR and fill it with 1000 values, randomly generated betweek 1000 and 9999 using DBMS_RANDOM package.

ALTER TABLE SAM_STR ADD
(MAIN_CAT VARCHAR2(20))

UPDATE SAM_STR
SET MAIN_CAT = (case WHEN VAL > 7500 THEN 'C' WHEN VAL > 5000 THEN 'B' WHEN VAL > 2500 THEN 'A' ELSE 'D' end)

We now add a new column to this table, MAIN_CAT, which is then updated based on a condition against the VAL column of the table. More precisely, the category is set as 'C' if the number is > 7500, 'B' if it is greater than 5000, A if it is greater than 2500 and D otherwise.

Just to provide another dimension, we add another column to this table. EVEN_ODD. As the name goes, this column takes the value of 'E' if the number is even, else it takes a value of 'O' if the number is ODD. So, we have

alter table sam_str add
(even_odd varchar2(2))

UPDATE SAM_STR SET EVEN_ODD=DECODE(MOD(VAL,2),0,'E','O')

So, we have two dimensions to our table. One that categorizes the numbers as even or odd in a separate column. The other one, divides the table into categories based on the value read. Let us start with the simplest of all, GROUP BY.

SELECT MAIN_CAT,COUNT(*) FROM SAM_STR GROUP BY MAIN_CAT

A    276
B    261
C    292
D    171

As it goes, we take a count of all the different categories in this SQL. The output is also provide along with the Query. When you run this Query, you may get a different output, as the numbers are randomly generated.Let us introduce ROLLUP now.

SELECT MAIN_CAT,COUNT(*) FROM SAM_STR GROUP BY ROLLUP(MAIN_CAT)

A    276
B    261
C    292
D    171
    1000 => Additional row that gives the complete total.

At this point, ROLLUP produces an additional row (result set). It is good to note that, if the GROUP BY contains one column, the result of ROLLUP will contain 2 sets. (n+1). So, ROLLUP performs something in addition to what GROUP BY does, which is a running total count in this case.Let us now add one more dimension to the SELECT statement and observe the output.

SELECT MAIN_CAT, EVEN_ODD,COUNT(*) FROM SAM_STR GROUP BY MAIN_CAT,EVEN_ODD

A    E    137
A    O    139
B    E    135
B    O    126
C    E    167
C    O    125
D    E    77
D    O    94

Here, we get the number of even /odd numbers against every category. When we include a ROLLUP here, the SQL changes to 

SELECT MAIN_CAT, EVEN_ODD,COUNT(*) FROM SAM_STR GROUP BY ROLLUP(MAIN_CAT,EVEN_ODD)

and the output is; [ I have used different colors to identify different sets]

A    E    137 => standard Output 1
A    O    139 => standard Output 1
A          276 => Output of ROLLUP. Even/ ODD ignored and the total at category
B    E    135 =>standard Output 1

B    O    126 => standard Output 1
B          261=>Output of ROLLUP. Even/ ODD ignored and the total at category
C    E    167=> standard Output 1

C    O    125=> standard Output 1
C          292=>Output of ROLLUP. Even/ ODD ignored and the total at category
D    E    77=>standard Output 1

D    O    94=>standard Output 1
D          171=>Output of ROLLUP. Even/ ODD ignored and the total at category
        1000 =>Complete Total. Output of ROLLUP

So, we have use two columns in the ROLLUP and we have got 3 (n+1) sets of result here. Note that ROLLUP moves from LEFT to RIGHT in the column order, and interchanging them, gives a different result, as shown below

SELECT EVEN_ODD,MAIN_CAT, COUNT(*) FROM SAM_STR GROUP BY ROLLUP(EVEN_ODD,MAIN_CAT)

E    A    137
E    B    135
E    C    167
E    D    77
E          516
O    A    139
O    B    126
O    C    125
O    D    94
O          484
           1000

This can be interpreted on the same lines as of the previous example. Total count result set is still the same. However, the second set of results gives the total against total count of even and odd numbers only. In the previous example, it was against individual categories. In the subsequent posts, let us dwell on how to use CUBE with examples.