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
Salve
ReplyDeleteSeems 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
Hi Dayakar,
ReplyDeleteA 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