# How to produce group subtotals for all combinations of the dimensions specified in Oracle?

Problem Statement:
You want to find out subtotals for all combinations of the dimensions specified in Oracle.

Solution:
The CUBE function 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.

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('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
............................
............................

Let us apply now CUBE function to the atp player - “Roger Federer”

## Example

SELECT player,title_type, SUM(titles) AS total_titles
FROM atp_titles
WHERE player = 'Roger Federer'
GROUP BY CUBE (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 ATP Tour Titles 103 Grandslams 20 123

In addition to the subtotals generated by the ROLLUP extension, the CUBE extension had generated subtotals for all combinations of the player and title type specified.The output from the CUBE is highlighted in bold above.

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

## Example

SELECT player,title_type, SUM(titles) AS total_titles
FROM atp_titles
GROUP BY CUBE (player,title_type)
ORDER BY player,title_type 
 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 ATP Tour Titles 503 Grandslams 83 586