- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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('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 ............................ ............................
Let us apply now CUBE function to the atp player - “Roger Federer”
SQL:
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 |