SELECT ITEM1, ITEM2, ITEM3, SUM(R1QTY) R1QTY, SUM(R2QTY) R2QTY, SUM(TTLQTY) TTLQTY
FROM (
SELECT ITEM1, ITEM2, ITEM3, SUM(QTY) R1QTY, 0 R2QTY, 0 TTLQTY
FROM TABLE_K
WHERE GUBUN = '1'
GROUP BY ITEM1, ITEM2, ITEM3
UNION ALL
SELECT ITEM1, ITEM2, ITEM3, 0 R1QTY, SUM(QTY) R2QTY, 0 TTLQTY
FROM TABLE_K
WHERE GUBUN = '2'
GROUP BY ITEM1, ITEM2, ITEM3
UNION ALL
SELECT ITEM1, ITEM2, ITEM3, 0 R1QTY, 0 R2QTY, SUM(QTY) TTLQTY
FROM TABLE_K
GROUP BY ITEM1, ITEM2, ITEM3
)
GROUP BY ITEM1, ITEM2, ITEM3 ;
위 쿼리를 좀더 간단하게 어떻게 바꿀수 있나요?
gubun 별로 수량을 SUM하고, 전체를 SUM 한 값을 각각의 컬럼으로 나타내고자 합니다.
|