Custom Rollups in Oracle: group by grouping sets and cubes
I get tons of email from mail lists that at one point or another I signed up for. This morning I received a mail titled "DBANews: DBASupport.com Newsletter for November 15, 2006" from DBASupport.com. As usual I scanned through this one because sometimes I find nuggets of gold in that email and sure enough today I found something.
I've read a few times about different options for the Group By aggregation features introduced in the later versions of Oracle but like most folks I didn't run out and try it out (just too busy, what else is new). Today's newsletter pointed to a link on builder.com titled "Group by GROUPING SETS for custom rollups in Oracle". After reading the article I was still a bit confused so I decided to give it a try (I guess I feel like learning something today).
I decided the easiest way to do this would be to try it out in the SCOTT schema on the EMP table. But....this schema no longer exists in the default installation of 10g. Ug... So...I found this link which tells me how to install the schema and the objects and viola...SCOTT is alive and kicking.
I then did a simple group by for the JOB column and the MGR column.
I then tried the CUBE...
Last I tried the GROUPING SETS...
Now the GROUPING SETS, that one looks promising. I bet theres tons of times where I do this in two sql statements and now I only have to do it in one (the less SQL the better).
Until next time...Rich
I've read a few times about different options for the Group By aggregation features introduced in the later versions of Oracle but like most folks I didn't run out and try it out (just too busy, what else is new). Today's newsletter pointed to a link on builder.com titled "Group by GROUPING SETS for custom rollups in Oracle". After reading the article I was still a bit confused so I decided to give it a try (I guess I feel like learning something today).
I decided the easiest way to do this would be to try it out in the SCOTT schema on the EMP table. But....this schema no longer exists in the default installation of 10g. Ug... So...I found this link which tells me how to install the schema and the objects and viola...SCOTT is alive and kicking.
SCOTT.SALT> select ename, job, mgr, sal from emp;
ENAME JOB MGR SAL
---------- --------- -------------- --------------
SMITH CLERK 7902 800
ALLEN SALESMAN 7698 1600
WARD SALESMAN 7698 1250
JONES MANAGER 7839 2975
MARTIN SALESMAN 7698 1250
BLAKE MANAGER 7839 2850
CLARK MANAGER 7839 2450
SCOTT ANALYST 7566 3000
KING PRESIDENT 5000
TURNER SALESMAN 7698 1500
ADAMS CLERK 7788 1100
JAMES CLERK 7698 950
FORD ANALYST 7566 3000
MILLER CLERK 7782 1300
I then did a simple group by for the JOB column and the MGR column.
SCOTT.SALT> select job, mgr, sum(sal)
2 from emp
3 group by job, mgr;
JOB MGR SUM(SAL)
--------- -------------- --------------
CLERK 7902 800
PRESIDENT 5000
CLERK 7698 950
CLERK 7788 1100
CLERK 7782 1300
SALESMAN 7698 5600
MANAGER 7839 8275
ANALYST 7566 6000
8 rows selected.
I then tried the CUBE...
SCOTT.SALT> select job, mgr, sum(sal)
2 from emp
3 group by cube(job, mgr);
JOB MGR SUM(SAL)
--------- -------------- --------------
5000
29025
7566 6000
7698 6550
7782 1300
7788 1100
7839 8275
7902 800
CLERK 4150
CLERK 7698 950
CLERK 7782 1300
CLERK 7788 1100
CLERK 7902 800
ANALYST 6000
ANALYST 7566 6000
MANAGER 8275
MANAGER 7839 8275
SALESMAN 5600
SALESMAN 7698 5600
PRESIDENT 5000
PRESIDENT 5000
21 rows selected.
Last I tried the GROUPING SETS...
SCOTT.SALT> select job, mgr, sum(sal)
2 from emp
3 group by grouping sets (job, mgr);
JOB MGR SUM(SAL)
--------- -------------- --------------
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000
7839 8275
5000
7782 1300
7698 6550
7902 800
7566 6000
7788 1100
12 rows selected.
Now the GROUPING SETS, that one looks promising. I bet theres tons of times where I do this in two sql statements and now I only have to do it in one (the less SQL the better).
Until next time...Rich
Comments