How to produce group subtotals and a grand total in Oracle?

OracleSoftware & CodingProgramming

Problem Statement:
You want to find out totals, subtotals and a grand total in Oracle.

Solution:
Oracle ROLLUP function performs grouping at multiple levels, using a right to left method of rolling up through intermediate levels to any grand total. To demonstrate the ROLLUP function we will create a table to hold tennis player along with the ATP tour titles and Grandslam titles acheived by the player.

We will begin by create the necessary data for this requirement.

Example

-- Drop table DROP TABLE atp_titles; -- Create table CREATE TABLE atp_titles (   player             VARCHAR2(100) NOT NULL,   title_type         VARCHAR2(100) NOT NULL,   titles             NUMBER NOT NULL);

Example

-- insert  ATP tour titles won by the player
INSERT INTO atp_titles VALUES('Roger Federer','ATP Tour Titles',103);
INSERT INTO atp_titles VALUES('Rafael Nadal','ATP Tour Titles',86);
INSERT INTO atp_titles VALUES('Novak Djokovic','ATP Tour Titles',81);
INSERT INTO atp_titles VALUES('Pete Sampras','ATP Tour Titles',64);
INSERT INTO atp_titles VALUES('Andre Agassi','ATP Tour Titles',52);
INSERT INTO atp_titles VALUES('Andy Murray','ATP Tour Titles',46);
INSERT INTO atp_titles VALUES('Thomas Muster','ATP Tour Titles',39);
INSERT INTO atp_titles VALUES('Andy Roddick','ATP Tour Titles',32);

Example

-- insert  grandslam titles won by the player
INSERT INTO atp_titles VALUES('Roger Federer','Grandslams',20);
INSERT INTO atp_titles VALUES('Rafael Nadal','Grandslams',20);
INSERT INTO atp_titles VALUES('Novak Djokovic','Grandslams',17);
INSERT INTO atp_titles VALUES('Pete Sampras','Grandslams',14);
INSERT INTO atp_titles VALUES('Andre Agassi','Grandslams',8);
INSERT INTO atp_titles VALUES('Andy Murray','Grandslams',3);
INSERT INTO atp_titles VALUES('Thomas Muster','Grandslams',1);
INSERT INTO atp_titles VALUES('Andy Roddick','Grandslams',0);

COMMIT;

Now we will look at few records inserted into atp_titles table.

Example

SELECT * FROM atp_titles ORDER BY 1;

Output

Andre Agassi    ATP Tour Titles     52
Andre Agassi    Grandslams          8
Andy Murray Grandslams          3
Andy Murray ATP Tour Titles     46
Andy Roddick    ATP Tour Titles     32
Andy Roddick    Grandslams          0
............................
............................

Oracle ROLLUP expression produces group subtotals from right to left along with grand total. With above data, let’s say we wanted to identify the total titles (i.e. ATP tour titles + Grandslam Titles) acheived by player “Roger Federer”.

Example

SELECT player,title_type, SUM(titles) AS total_titles   FROM atp_titles  WHERE player = 'Roger Federer' GROUP BY ROLLUP (player,title_type) ORDER BY player,title_type ;

Output

player
title_type
total_titles
Roger Federer
ATP Tour Titles
103
Roger Federer
Grandslams
20
Roger Federer
 
123
 
 
123

ROLLUP produces n+1 levels of subtotals for “n” number of columns listed in the ROLLUP. In above example after performing normal grouping by player and title_type, the ROLLUP function rolls up all title_type values so that we see sum for the Grandslams level for the player “Roger Federer”. You can see the rolled up rows in bold in the output.

Now we will apply the ROLLUP function for all the players in the table as below:

SQL:

Example

SELECT player, title_type, SUM(titles) As total   FROM atp_titles GROUP BY ROLLUP (player,title_type) ORDER BY player,title_type;

Output

player
title_type
total_titles
Andre Agassi
ATP Tour Titles
52
Andre Agassi
Grandslams
8
Andre Agassi
 
60
Andy Murray
ATP Tour Titles
46
Andy Murray
Grandslams
3
Andy Murray
 
49
Andy Roddick
ATP Tour Titles
32
Andy Roddick
Grandslams
0
Andy Roddick
 
32
Novak Djokovic
ATP Tour Titles
81
Novak Djokovic
Grandslams
17
Novak Djokovic
 
98
Pete Sampras
ATP Tour Titles
64
Pete Sampras
Grandslams
14
Pete Sampras
 
78
Rafael Nadal
ATP Tour Titles
86
Rafael Nadal
Grandslams
20
Rafael Nadal
 
106
Roger Federer
ATP Tour Titles
103
Roger Federer
Grandslams
20
Roger Federer
 
123
Thomas Muster
ATP Tour Titles
39
Thomas Muster
Grandslams
1
Thomas Muster
 
40
 
 
586

ROLLUP functions allows us to perform partial rollup to reduce the number of subtotals calculated. The output from the following partial rollup is shown below:

SQL:

Example

SELECT player, title_type, SUM(titles) As total   FROM atp_titles GROUP BY ROLLUP (player,title_type) ORDER BY player,title_type;

Output

player
title_type
total_titles
Andre Agassi
ATP Tour Titles
52
Andre Agassi
Grandslams
8
Andre Agassi
 
60
Andy Murray
ATP Tour Titles
46
Andy Murray
Grandslams
3
Andy Murray
 
49
Andy Roddick
ATP Tour Titles
32
Andy Roddick
Grandslams
0
Andy Roddick
 
32
Novak Djokovic
ATP Tour Titles
81
Novak Djokovic
Grandslams
17
Novak Djokovic
 
98
Pete Sampras
ATP Tour Titles
64
Pete Sampras
Grandslams
14
Pete Sampras
 
78
Rafael Nadal
ATP Tour Titles
86
Rafael Nadal
Grandslams
20
Rafael Nadal
 
106
Roger Federer
ATP Tour Titles
103
Roger Federer
Grandslams
20
Roger Federer
 
123
Thomas Muster
ATP Tour Titles
39
raja
Published on 04-Dec-2020 10:24:31
Advertisements