Home > SQL, Technical > CUBE, ROLLUP, GROUPING in ORACLE SQL

CUBE, ROLLUP, GROUPING in ORACLE SQL

Basic table used in the Examples

SQL:create table dining(dining_id number primary key,food varchar2(20),food_type varchar2(20),qty number);

DINING_ID FOOD FOOD_TYPE QTY

SQL:

insert into dining values(1,’Dosa’,’Masala Dosa’,20);
insert into dining values(2,’Dosa’,’Tomato Dosa’,20);
insert into dining values(3,’Dosa’,’Onion Dosa’,30);
insert into dining values(4,’Dosa’,’Plain Dosa’,40);
insert into dining values(5,’Idli’,’Rava Idli’,50);
insert into dining values(6,’Idli’,’Rice Idli’,60);
insert into dining values(7,’Idli’,’Aval Idli’,70);

DINING_ID FOOD FOOD_TYPE QTY
1 Dosa Masala Dosa 10
2 Dosa Tomato Dosa 20
3 Dosa Onion Dosa 30
4 Dosa Plain Dosa 40
5 Idli Rava Idli 50
6 Idli Rice Idli 60
7 Idli Aval Idli 70

Using Group By:

SELECT

FOOD,FOOD_TYPE,SUM(QTY)

FROM

DINING

GROUP BY FOOD, FOOD_TYPE

ORDER BY FOOD, FOOD_TYPE;

OUTPUT:-

FOOD FOOD_TYPE SUM(QTY)
Dosa Masala Dosa 10
Dosa Onion Dosa 30
Dosa Plain Dosa 40
Dosa Tomato Dosa 20
Idli Aval Idli 70
Idli Rava Idli 50
Idli Rice Idli 60

USING ROLLUP WITH GROUP BY:-

SELECT

FOOD,FOOD_TYPE,SUM(QTY)

FROM

DINING

GROUP BY ROLLUP(FOOD, FOOD_TYPE)

ORDER BY FOOD, FOOD_TYPE;

FOOD FOOD_TYPE SUM(QTY)
Dosa Masala Dosa 10
Dosa Onion Dosa 30
Dosa Plain Dosa 40
Dosa Tomato Dosa 20
Dosa 100
Idli Aval Idli 70
Idli Rava Idli 50
Idli Rice Idli 60
Idli 180
280

When we use the rollup function with group by, output will have aggregate of the rows.
USING CUBE WITH GROUP BY:-

SELECT

FOOD,FOOD_TYPE,SUM(QTY)

FROM

DINING

GROUP BY CUBE(FOOD, FOOD_TYPE)

ORDER BY FOOD, FOOD_TYPE;

FOOD FOOD_TYPE SUM(QTY)
Dosa Masala Dosa 10
Dosa Onion Dosa 30
Dosa Plain Dosa 40
Dosa Tomato Dosa 20
Dosa 100
Idli Aval Idli 70
Idli Rava Idli 50
Idli Rice Idli 60
Idli 180
Aval Idli 70
Masala Dosa 10
Onion Dosa 30
Plain Dosa 40
Rava Idli 50
Rice Idli 60
Tomato Dosa 20
280

Cube will result aggregate values along with super aggregate values
USING GROUPING WITH GROUP BY:-

SELECT

GROUPING(FOOD),FOOD_TYPE,SUM(QTY)

FROM

DINING

GROUP BY CUBE(FOOD, FOOD_TYPE)

ORDER BY FOOD, FOOD_TYPE;

GROUPING(FOOD) FOOD_TYPE SUM(QTY)
0 Masala Dosa 10
0 Onion Dosa 30
0 Plain Dosa 40
0 Tomato Dosa 20
0 100
0 Aval Idli 70
0 Rava Idli 50
0 Rice Idli 60
0 180
1 Aval Idli 70
1 Masala Dosa 10
1 Onion Dosa 30
1 Plain Dosa 40
1 Rava Idli 50
1 Rice Idli 60
1 Tomato Dosa 20
1 280

SELECT

grouping(FOOD),FOOD_TYPE,SUM(QTY)

FROM

DINING

GROUP BY ROLLUP(FOOD, FOOD_TYPE)

ORDER BY FOOD, FOOD_TYPE;

GROUPING(FOOD) FOOD_TYPE SUM(QTY)
0 Masala Dosa 10
0 Onion Dosa 30
0 Plain Dosa 40
0 Tomato Dosa 20
0 100
0 Aval Idli 70
0 Rava Idli 50
0 Rice Idli 60
0 180
1 280

GROUPING function will let us know which is aggregate rows and which is not.

(If there is a value available for that field means zero otherwise one)

Advertisements
Categories: SQL, Technical
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: