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
합계 - ------ ----- 총합계금액
좋은 답변 부탁드립니다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | 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 문장이면 도움 되실거 같네요.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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)) ; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | -- 전체 합계는 그룹바이 없이 하면 됩니다. -- 정렬구문은 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 ; |