Example of ROLLUP and CUBE in Oracle

DROP TABLE dimension_tab;

CREATE TABLE dimension_tab (
  fid1   NUMBER NOT NULL,
  fid2   NUMBER NOT NULL,
  fid3   NUMBER NOT NULL,
  fid4   NUMBER NOT NULL,
  sales_value NUMBER(10,2) NOT NULL
);

INSERT INTO dimension_tab
SELECT TRUNC(DBMS_RANDOM.value(1,3)) AS fid1,
       TRUNC(DBMS_RANDOM.value(1,6)) AS fid2,
       TRUNC(DBMS_RANDOM.value(1,11)) AS fid3,
       TRUNC(DBMS_RANDOM.value(1,11)) AS fd4,
       ROUND(DBMS_RANDOM.value(1,100), 2) AS sales_value
FROM   dual
CONNECT BY level <= 1000;
COMMIT;

Note:
fid1 generate value 1 and 2
fid2 generate value 1,2,3,4 and 5;
fid3 generate value 1 to 10;
fid4 generate value 1 to 10;

sum of sales_value always shold be same.
num_rows always should be 1000

---Find total sales value---

SELECT SUM(sales_value) AS sales_value
FROM   dimension_tab;

Including the first two columns in the GROUP BY clause should give us 10 rows (2*5), 
each with its aggregated values.

SELECT fid1,
       COUNT(*) AS num_rows,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY fid1
ORDER BY fid1;

SELECT fid1,
       fid2,
       COUNT(*) AS num_rows,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY fid1, fid2
ORDER BY fid1, fid2;

Including the first three columns in the GROUP BY clause should give us 100 rows (2*5*10).


SELECT fid1,
       fid2,
  fid3,
       COUNT(*) AS num_rows,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY fid1, fid2,fid3
ORDER BY fid1, fid2,fid3;

----ROLLUP----
In addition to the regular aggregation results we expect from the GROUP BY clause, 
the ROLLUP extension produces group subtotals from right to left and a grand total. 
If "n" is the number of columns listed in the ROLLUP, there will be n+1 levels of subtotals

SELECT fid1,
       fid2,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY ROLLUP (fid1, fid2)
ORDER BY fid1, fid2;

SELECT fid1,
       fid2,
       fid3,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY ROLLUP (fid1, fid2, fid3)
ORDER BY fid1, fid2, fid3;


It is possible to do a partial rollup to reduce the number of subtotals calculated. 
The output from the following partial rollup is shown here.

SELECT fid1,
       fid2,
       fid3,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY fid1, ROLLUP (fid2, fid3)
ORDER BY fid1, fid2, fid3;

----CUBE----

In addition to the subtotals generated by the ROLLUP extension, the CUBE extension 

will generate subtotals for all combinations of the dimensions specified. If "n" is 

the number of columns listed in the CUBE, there will be 2n subtotal combinations.

SELECT fid1,
       fid2,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY CUBE (fid1, fid2)
ORDER BY fid1, fid2;

SELECT fid1,
       fid2,
       fid3,
       SUM(sales_value) AS sales_value
FROM   dimension_tab
GROUP BY CUBE (fid1, fid2, fid3)
ORDER BY fid1, fid2, fid3;