select decode(gubun, 1, dname, 2, '계') DNAME,
		sum(clerk) CLERK,
		sum(salesman) SALESMAN,
		sum(manager) MANAGER,
		sum(etc) ETC,
		sum(sal) SAL
from
	(select dname, clerk, salesman, manager, etc, sal
	from dept, (select deptno,
					   sum(decode(job, 'CLERK', sal)) CLERK,
					   sum(decode(job, 'SALESMAN', sal)) SALESMAN,
					   sum(decode(job, 'MANAGER', sal)) MANAGER,
					   sum(decode(job, 'ANALYST', sal, 'PRESIDENT', sal)) ETC,
					   sum(sal) SAL
				from emp
				group by deptno) emp
	where dept.deptno = emp.deptno) A,
	(select 1 gubun from dual
	union
	select 2 gubun from dual) B
group by decode(gubun, 1, dname, 2, '계')


DNAME               CLERK   SALESMAN    MANAGER        ETC        SAL
-------------- ---------- ---------- ---------- ---------- ----------
ACCOUNTING           1300                  2450       5000       8750
RESEARCH             1900                  2975       6000      10875
SALES                 950       5600       2850                  9400
계                   4150       5600       8275      11000      29025
4 rows selected













--합계를 다른 방법으로
select nvl(decode(gubun, 1, dname), '합계') DNAME,
		sum(clerk) CLERK,
		sum(salesman) SALESMAN,
		sum(manager) MANAGER,
		sum(etc) ETC,
		sum(sal) SAL
from
	(select dname, clerk, salesman, manager, etc, sal 
	from dept, (select deptno,
					   sum(decode(job, 'CLERK', sal)) CLERK,
					   sum(decode(job, 'SALESMAN', sal)) SALESMAN,
					   sum(decode(job, 'MANAGER', sal)) MANAGER,
					   sum(decode(job, 'ANALYST', sal, 'PRESIDENT', sal)) ETC,
					   sum(sal) SAL
				from emp
				group by deptno) emp
	where dept.deptno = emp.deptno) A,
	(select 1 gubun from dual
	union
	select 2 gubun from dual) B
group by decode(gubun, 1, dname)