select T2.ComName AS 직급
, T3.ComName AS 성별
, T5.SalDT AS 지급일
, T6.SalName AS 공제명
, Sum(T5.Money) AS 금액
from Employee T1
Inner join ComCode T2
On T1.Grade = T2.ComCD
Inner join ComCode T3
On T1.Gender = T3.ComCD
Inner join Salary T4
On T1.EmpNO = T4.EmpNO
Inner join SalDetail T5
On T4.EmpNO = T5.EmpNO
and T4.SalDT = T5.SalDT
Inner join SalComCode T6
On T5.SalCode = T6.SalCode
where T2.CodeGroupCD = 'H1'
and T3.CodeGroupCD = 'H2'
and T6.SalDIV = 'D'
and T5.SalDT like '2013%'
group by T5.SalDT, T2.ComName, T3.ComName, T6.SalName
order by T2.ComName desc
, T3.comName asc
, T5.SalDT
, T6.SalName
위의 쿼리를 이용하여 금액 합산을 구하려 합니다. 어제 질문했던 RollUP을 쓰는게 아닌 Union을 이용하라는데 두번째 쿼리에서 어떻게 넣어야 금액 합계만 맨 밑줄에 보이게 할 수 있나요??
과장 남 20130120 건강보험 300000
과장 남 20130120 고용보험 450000
과장 남 20130120 국민연금 753000
과장 남 20130120 기타공제 480000
과장 여 20130120 건강보험 100000
과장 여 20130120 고용보험 140000
과장 여 20130120 국민연금 245000
과장 여 20130120 기타공제 160000
합계 - ------ ----- 총합계금액
좋은 답변 부탁드립니다.
select '합계', '','' ,'' , sum(금액) as 합계 (select T2.ComName AS 직급 , T3.ComName AS 성별 , T5.SalDT AS 지급일 , T6.SalName AS 공제명 , Sum(T5.Money) AS 금액 from Employee T1 Inner join ComCode T2 On T1.Grade = T2.ComCD Inner join ComCode T3 On T1.Gender = T3.ComCD Inner join Salary T4 On T1.EmpNO = T4.EmpNO Inner join SalDetail T5 On T4.EmpNO = T5.EmpNO and T4.SalDT = T5.SalDT Inner join SalComCode T6 On T5.SalCode = T6.SalCode where T2.CodeGroupCD = 'H1' and T3.CodeGroupCD = 'H2' and T6.SalDIV = 'D' and T5.SalDT like '2013%' group by T5.SalDT, T2.ComName, T3.ComName, T6.SalName)
union all밑에다가 붙이시면됨
요롷게하면 가능할꺼가튼데요?
group by ((T5.SalDT, T2.ComName, T3.ComName, T6.SalName))
와
직급쪽에 CASE 문 넣으면 되요.
아래 예제 SQL 문장이면 도움 되실거 같네요.
WITH T (JOB, SEX, SAL_DT, SAL_NM, AMT) AS ( SELECT '과장1', '남', '20130120', '건강보험', 300000 FROM DUAL UNION ALL SELECT '과장2', '남', '20130120', '고용보험', 450000 FROM DUAL UNION ALL SELECT '과장3', '남', '20130120', '국민연금', 753000 FROM DUAL UNION ALL SELECT '과장4', '남', '20130120', '기타공제', 480000 FROM DUAL UNION ALL SELECT '과장5', '여', '20130120', '건강보험', 100000 FROM DUAL UNION ALL SELECT '과장6', '여', '20130120', '고용보험', 140000 FROM DUAL UNION ALL SELECT '과장8', '여', '20130120', '국민연금', 245000 FROM DUAL UNION ALL SELECT '과장9', '여', '20130120', '기타공제', 160000 FROM DUAL ) SELECT CASE WHEN GROUPING_ID(JOB, SEX, SAL_DT, SAL_NM, AMT) > 0 THEN '합계' ELSE JOB END JOB , SEX, SAL_DT, SAL_NM, SUM(AMT) , GROUPING_ID(JOB, SEX, SAL_DT, SAL_NM, AMT) AS G_ID FROM T GROUP BY ROLLUP ((JOB, SEX, SAL_DT, SAL_NM, AMT)) ;
-- 전체 합계는 그룹바이 없이 하면 됩니다. -- 정렬구문은 Union 이 불가하니 정렬을 맨 뒤로 빼세요. SELECT t2.comname AS 직급 , t3.comname AS 성별 , t5.saldt AS 지급일 , t6.salname AS 공제명 , SUM(t5.money) AS 금액 FROM employee t1 INNER JOIN ComCode t2 ON t1.grade = t2.comcd INNER JOIN comcode t3 ON t1.gender = t3.comcd INNER JOIN salary t4 ON t1.empno = t4.empno INNER JOIN saldetail t5 ON t4.empno = t5.empno AND t4.saldt = t5.saldt INNER JOIN salcomcode t6 ON t5.salcode = t6.salcode WHERE t2.codegroupcd = 'H1' AND t3.codegroupcd = 'H2' AND t6.saldiv = 'D' AND t5.saldt LIKE '2013%' GROUP BY t2.comname, t3.comname, t5.saldt, t6.salname UNION ALL SELECT '합계' AS 직급 , Null AS 성별 , Null AS 지급일 , Null AS 공제명 , SUM(t5.money) AS 금액 FROM employee t1 INNER JOIN ComCode t2 ON t1.grade = t2.comcd INNER JOIN comcode t3 ON t1.gender = t3.comcd INNER JOIN salary t4 ON t1.empno = t4.empno INNER JOIN saldetail t5 ON t4.empno = t5.empno AND t4.saldt = t5.saldt INNER JOIN salcomcode t6 ON t5.salcode = t6.salcode WHERE t2.codegroupcd = 'H1' AND t3.codegroupcd = 'H2' AND t6.saldiv = 'D' AND t5.saldt LIKE '2013%' ORDER BY 1 DESC, 2, 3, 4 ;