Loading

Friday, October 28, 2011

ORACLE SQL TUTORIAL: ROLLUP AND CUBE




USING ROLLUP

This will give the salaries in each department in each job category along wih the total salary   
fot individual departments and the total salary of all the departments.

SQL> Select deptno,job,sum(sal) from emp group by rollup(deptno,job);

                                DEPTNO   JOB         SUM(SAL)
----------  ---------   ----------
        10    CLERK               1300
        10    MANAGER     2450
        10    PRESIDENT     5000
        10                               8750
        20    ANALYST        6000
        20    CLERK              1900
        20    MANAGER     2975
        20                               10875
        30    CLERK               950
        30    MANAGER      2850
        30    SALESMAN     5600
        30                                9400
                            29025

USING GROUPING

 In the above query it will give the total salary of the individual departments but with a  
 blank in the job column and gives the total salary of all the departments with blanks in
 deptno and job columns.
 
 To replace these blanks with your desired string grouping will be used
  SQL> select decode(grouping(deptno),1,'All Depts',deptno),decode(grouping(job),1,'All             
          jobs',job),sum(sal) from emp group by rollup(deptno,job);

DECODE(GROUPING(DEPTNO),1,'ALLDEPTS',DEP DECODE(GR   SUM(SAL)
-----------------------------------  ----------------------------------    --------------
10                                                           CLERK                    1300
10                                                           MANAGER            2450
10                                                           PRESIDENT          5000
10                                                           All jobs                 8750
20                                                           ANALYST             6000
20                                                           CLERK                    1900
20                                                           MANAGER         2975
20                                                           All jobs                 10875
30                                                           CLERK                      950
30                                                           MANAGER         2850
30                                                           SALESMAN        5600
30                                                           All jobs                 8400
All Depts                                             All jobs                  29025

   Grouping will return 1 if the column which is specified in the grouping function has been
   used in rollup.
   Grouping will be used in association with decode.

USING CUBE

This will give the salaries in each department in each job category, the total salary for individual departments, the total salary of all the departments and the salaries in each job category.

SQL> select decode(grouping(deptno),1,’All Depts’,deptno),decode(grouping(job),1,’All 
        Jobs’,job),sum(sal) from emp group by cube(deptno,job);

DECODE(GROUPING(DEPTNO),1,'ALLDEPTS',DEP DECODE(GR   SUM(SAL)
-----------------------------------  ------------------------------------  ------------
10                                           CLERK                                    1300
10                                           MANAGER                          2450
10                                           PRESIDENT                          5000
10                                           All Jobs                                8750
20                                           ANALYST                             6000
20                                           CLERK                                    1900
20                                           MANAGER                          2975
20                                           All Jobs                                10875
30                                           CLERK                                      950
30                                           MANAGER                           2850
30                                           SALESMAN                         5600
30                                           All Jobs                                9400
All Depts                             ANALYST                             6000
All Depts                             CLERK                                    4150
All Depts                             MANAGER                          8275
All Depts                             PRESIDENT                          5000
All Depts                             SALESMAN                         5600
All Depts                             All Jobs                                29025



1 Responses to “ORACLE SQL TUTORIAL: ROLLUP AND CUBE”

Sridevi K said...
October 8, 2016 at 12:58 AM

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.