You Asked
Tom
I'm trying to write a query which gives a grand total, as well as sub totals for a couple of columns.
SQL> ed
Wrote file afiedt.buf
1 select ename,deptno, mgr, sum(sal)
2 from emp
3* group by rollup(ename,deptno, mgr)
SQL> /
ENAME DEPTNO MGR SUM(SAL)
---------- ---------- ---------- ----------
FORD 20 7566 3000
FORD 20 3000
FORD 3000
KING 10 5000
KING 10 5000
KING 5000
WARD 30 7698 1250
WARD 30 1250
WARD 1250
ADAMS 20 7788 1100
ADAMS 20 1100
ENAME DEPTNO MGR SUM(SAL)
---------- ---------- ---------- ----------
ADAMS 1100
ALLEN 30 7698 1600
ALLEN 30 1600
ALLEN 1600
BLAKE 30 7839 2850
BLAKE 30 2850
BLAKE 2850
CLARK 10 7839 2450
CLARK 10 2450
CLARK 2450
JAMES 30 7698 950
ENAME DEPTNO MGR SUM(SAL)
---------- ---------- ---------- ----------
JAMES 30 950
JAMES 950
JONES 20 7839 2975
JONES 20 2975
JONES 2975
SCOTT 20 7566 3000
SCOTT 20 3000
SCOTT 3000
SMITH 20 7902 800
SMITH 20 800
SMITH 800
ENAME DEPTNO MGR SUM(SAL)
---------- ---------- ---------- ----------
MARTIN 30 7698 1250
MARTIN 30 1250
MARTIN 1250
MILLER 10 7782 1300
MILLER 10 1300
MILLER 1300
TURNER 30 7698 1500
TURNER 30 1500
TURNER 1500
29025
43 rows selected.
I want to see the ename in the output, but I should break on the ename, and should not see ename repeated. I just want the grand total, and sub totals for each MGR and DEPTNO with label 'sub total'.
How can I achieve this.
and Tom said...
well, I fail to see how you can have the mgr "subtotal" given that a manager could manage people in different DEPTNOS so if we sorted by DEPTNO/MGR -- a mgr would have people in different deptnos (where would that subtotal go???)
MGR=7839 fits that category. No way to get a "sub total" for a mgr by deptno and have that be the subtotal for the mgr in this case using simple aggregation since the MGR spans deptnos. (only if a mgr managed people IN a deptno would this work using aggregation)
But, we can get all of your required records:
group by grouping sets( (ename,deptno,mgr), (deptno), (mgr), () )
^^^^^^^^^^^^^^^^^^ the details
^^^^^^^^ by deptno
^^^^^ by mgr
^^ grand total
This gives you a feel for what you can do with grouping/aggregates -- but only if MGRS did not occurr across DEPTNOS would you be able to achieve your stated goal:
scott@ORA9IR2> select decode( bin_to_num( grouping( ename),grouping(deptno), grouping(mgr) ),
2 0, 'detail',
3 5, 'by deptno',
4 6, 'by mgr',
5 7, 'grand total' ) what,
6 ename,
7 deptno,
8 mgr,
9 sum(sal) ,
10 grouping(ename) gename,
11 grouping(deptno) gdeptno,
12 grouping(mgr) gmgr
13 from emp
14 group by grouping sets( (ename,deptno,mgr), (deptno), (mgr), () )
15 order by decode( bin_to_num( grouping( ename),grouping(deptno), grouping(mgr) ), 6, 1, 0 ),
16 deptno NULLS LAST, grouping( DEPTNO ) desc, ename
17 /
WHAT ENAME DEPTNO MGR SUM(SAL) GENAME GDEPTNO GMGR
----------- ------ ------ ----- ---------- ---------- ---------- ----------
detail CLARK 10 7839 2450 0 0 0
detail KING 10 5000 0 0 0
detail MILLER 10 7782 1300 0 0 0
by deptno 10 8750 1 0 1
detail ADAMS 20 7788 1100 0 0 0
detail FORD 20 7566 3000 0 0 0
detail JONES 20 7839 2975 0 0 0
detail SCOTT 20 7566 3000 0 0 0
detail SMITH 20 7902 800 0 0 0
by deptno 20 10875 1 0 1
detail ALLEN 30 7698 1600 0 0 0
detail BLAKE 30 7839 2850 0 0 0
detail JAMES 30 7698 950 0 0 0
detail MARTIN 30 7698 1250 0 0 0
detail TURNER 30 7698 1500 0 0 0
detail WARD 30 7698 1250 0 0 0
by deptno 30 9400 1 0 1
grand total 29025 1 1 1
by mgr 7566 6000 1 1 0
by mgr 7698 6550 1 1 0
by mgr 7782 1300 1 1 0
by mgr 7788 1100 1 1 0
by mgr 7839 8275 1 1 0
by mgr 7902 800 1 1 0
by mgr 5000 1 1 0
25 rows selected.
Rating
(12 ratings)
Is this answer out of date? If it is, please let us know via a Comment