# 부서별 급여 avg, 급여 sum 핵심은. Union all을 사용하지 않는 것... ^^

# 부서별급여 avg, 급여 sum : 이 문제의 핵심은... Union all을 사용하지 않는 것... ^^

-- 재료

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	80/12/17	800		20
7499	ALLEN	SALESMAN	7698	81/02/20	1600	300	30
7521	WARD	SALESMAN	7698	81/02/22	1250	500	30
7566	JONES	MANAGER	7839	81/04/02	2975		20
7654	MARTIN	SALESMAN	7698	81/09/28	1250	1400	30
7698	BLAKE	MANAGER	7839	81/05/01	2850		30
7782	CLARK	MANAGER	7839	81/06/09	2450		10
7788	SCOTT	ANALYST	7566	82/12/09	3000		20
7839	KING	PRESIDENT		81/11/17	5000		10
7844	TURNER	SALESMAN	7698	81/09/08	1500	0	30
7876	ADAMS	CLERK	7788	83/01/12	1100		20
7900	JAMES	CLERK	7698	81/12/03	950		30
7902	FORD	ANALYST	7566	81/12/03	3000		20
7934	MILLER	CLERK	7782	82/01/23	1300		10

-- 원하는 결과

GUB	D10	D20	D30
AVG	2916.66667	2175	1566.66667
SUM	8750	10875	9400

방법1 : 수업중 쉬는 시간에 잠시 생각해본 것... ^^

select b.*, a.*
from (select deptno,
sal,
decode(deptno, 10, sal) as d10,
decode(deptno, 20, sal) as d20,
decode(deptno, 30, sal) as d30
from emp) a,
(select 1 as no from dual
union all
select 2 from dual) b
order by b.no, a.deptno;

select b.no,a.deptno,
d10,
d20,
d30
from (select deptno,
sal,
decode(deptno, 10, sal) as d10,
decode(deptno, 20, sal) as d20,
decode(deptno, 30, sal) as d30
from emp) a,
(select 1 as no from dual
union all
select 2 from dual) b
order by b.no,a.deptno;

select b.no,a.deptno,
avg(d10), sum(d10),
avg(d20), sum(d20),
avg(d30), sum(d30)
from (select deptno,
sal,
decode(deptno, 10, sal) as d10,
decode(deptno, 20, sal) as d20,
decode(deptno, 30, sal) as d30
from emp) a,
(select 1 as no from dual
union all
select 2 from dual) b
group by b.no,a.deptno;

select no, deptno,
decode(no, 1, a10, s10) as d10,
decode(no, 1, a20, s20) as d20,
decode(no, 1, a30, s30) as d30
from (
select b.no,a.deptno,
avg(d10) a10, sum(d10) s10,
avg(d20) a20, sum(d20) s20,
avg(d30) a30, sum(d30) s30
from (select deptno,
sal,
decode(deptno, 10, sal) as d10,
decode(deptno, 20, sal) as d20,
decode(deptno, 30, sal) as d30
from emp) a,
(select 1 as no from dual
union all
select 2 from dual) b
group by b.no,a.deptno
);

select no,
max(decode(no, 1, a10, s10)) as d10,
max(decode(no, 1, a20, s20)) as d20,
max(decode(no, 1, a30, s30)) as d30
from (
select b.no,a.deptno,
avg(d10) a10, sum(d10) s10,
avg(d20) a20, sum(d20) s20,
avg(d30) a30, sum(d30) s30
from (select deptno,
sal,
decode(deptno, 10, sal) as d10,
decode(deptno, 20, sal) as d20,
decode(deptno, 30, sal) as d30
from emp) a,
(select 1 as no from dual
union all
select 2 from dual) b
group by b.no,a.deptno
)
group by no;

create view vu_avg_sum
as
select no,
max(decode(no, 1, a10, s10)) as d10,
max(decode(no, 1, a20, s20)) as d20,
max(decode(no, 1, a30, s30)) as d30
from (
select b.no,a.deptno,
avg(d10) a10, sum(d10) s10,
avg(d20) a20, sum(d20) s20,
avg(d30) a30, sum(d30) s30
from (select deptno,
sal,
decode(deptno, 10, sal) as d10,
decode(deptno, 20, sal) as d20,
decode(deptno, 30, sal) as d30
from emp) a,
(select 1 as no from dual
union all
select 2 from dual) b
group by b.no,a.deptno
)
group by no;

select decode(no, 1, 'AVG', 2, 'SUM') as gubun,
d10, d20, d30
from vu_avg_sum;

방법2 :퇴근길에 잠시 더 생각해보니... ^^

select *
from (select deptno, avg(sal) as avg_sal, sum(sal) as sum_sal
from emp
group by deptno);

select *
from (select deptno, avg(sal) as avg_sal, sum(sal) as sum_sal
from emp
group by deptno) a,
(select 1 as no from dual
union all
select 2 from dual) b;

select a.deptno, b.no,
avg_sal, sum_sal,
avg_sal, sum_sal,
avg_sal, sum_sal
from (select deptno, avg(sal) as avg_sal, sum(sal) as sum_sal
from emp
group by deptno) a,
(select 1 as no from dual
union all
select 2 from dual) b;

select a.deptno, b.no,
decode(a.deptno+b.no, 11, avg_sal, 12, sum_sal) as d10,
decode(a.deptno+b.no, 21, avg_sal, 22, sum_sal) as d20,
decode(a.deptno+b.no, 31, avg_sal, 32, sum_sal) as d30
from (select deptno, avg(sal) as avg_sal, sum(sal) as sum_sal
from emp
group by deptno) a,
(select 1 as no from dual
union all
select 2 from dual) b;

select a.deptno, b.no,
decode(a.deptno+b.no, 11, avg_sal, 12, sum_sal) as d10,
decode(a.deptno+b.no, 21, avg_sal, 22, sum_sal) as d20,
decode(a.deptno+b.no, 31, avg_sal, 32, sum_sal) as d30
from (select deptno, avg(sal) as avg_sal, sum(sal) as sum_sal
from emp
group by deptno) a,
(select 1 as no from dual
union all
select 2 from dual) b
order by b.no, a.deptno;

select b.no,
max(decode(a.deptno+b.no, 11, avg_sal, 12, sum_sal)) as d10,
max(decode(a.deptno+b.no, 21, avg_sal, 22, sum_sal)) as d20,
max(decode(a.deptno+b.no, 31, avg_sal, 32, sum_sal)) as d30
from (select deptno, avg(sal) as avg_sal, sum(sal) as sum_sal
from emp
group by deptno) a,
(select 1 as no from dual
union all
select 2 from dual) b
group by b.no;

select decode(no, 1, 'AVG', 2, 'SUM') as gubun,
max(decode(a.deptno+b.no, 11, avg_sal, 12, sum_sal)) as d10,
max(decode(a.deptno+b.no, 21, avg_sal, 22, sum_sal)) as d20,
max(decode(a.deptno+b.no, 31, avg_sal, 32, sum_sal)) as d30
from (select deptno, avg(sal) as avg_sal, sum(sal) as sum_sal
from emp
group by deptno) a,
(select 1 as no from dual
union all
select 2 from dual) b
group by b.no;

또 다른 방법은 없을까? ^^