Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Saturday, 2 April 2016

SQL Rollup Grouping Cube

These are the enhancements to the group by feature.

USING ROLLUP

This will give the salaries in each department in each job category along with the total salary for 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,'AllDepts',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                          9400
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 pts’,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
Share this article :

2 comments:

  1. Salve


    Seems like I won the lottery here….This is a treasure box of blogs and your folks are like leprechauns! Phenomenal read on SQL Rollup Grouping Cube !

    I'd like to be able to log all constraint errors when users are running an application having Oracle as the back-end. That means inserting a record into an Errors Log table in Oracle.

    Is that even possible? In an exception clause, I could do that; however, when a constraint fails, I don't know where I'd be able to run such code to do the insert.

    Is this even possible?

    Thanks for any help you can give.

    Wayne

    Appreciate your effort for making such useful blogs and helping the community.


    ,Merci

    ReplyDelete
  2. Hi Dayakar,

    A really interesting, clear and easily readable SQL Rollup Grouping Cube article of interesting and different perspectives.I will clap. So much is so well covered here.


    I have a job which picks a record from a cursor and then it calls a stored procedure which processes the
    record picked up from the cursor.

    The stored procedure has multiple queries to process the record. In all, procedure takes about 0.3 seconds to process a single record picked up by the cursor but since cursor contains more than 1 Lac records it takes hours to complete the job.

    The queries in the stored procedure are all optimized

    I was thinking of making the procedure run in multi threaded way as in java and other programming language.
    Can it be done in oracle? or is there any other way I can reduce the run time of my job.






    Follow my new blog if you interested in just tag along me in any social media platforms!


    ,Merci

    Ajeeth

    ReplyDelete