Q. 다음의 매출 실적에 대해, 매출 상세 내역과 함께 상품별 합계를 조회하는 쿼리를 작성하라.
[매출 데이터]
WITH T AS
(
SELECT 2006 YYYY, 1 MM, 'CRT' ITEM, 10 QTY FROM DUAL
UNION ALL SELECT 2006 , 1 , 'LCD' , 20 QTY FROM DUAL
UNION ALL SELECT 2006 , 2 , 'CRT' , 30 QTY FROM DUAL
UNION ALL SELECT 2006 , 2 , 'LCD' , 40 QTY FROM DUAL
UNION ALL SELECT 2007 , 1 , 'CRT' , 50 QTY FROM DUAL
UNION ALL SELECT 2007 , 1 , 'LCD' , 60 QTY FROM DUAL
UNION ALL SELECT 2007 , 2 , 'CRT' , 70 QTY FROM DUAL
UNION ALL SELECT 2007 , 2 , 'LCD' , 80 QTY FROM DUAL
)
(
SELECT 2006 YYYY, 1 MM, 'CRT' ITEM, 10 QTY FROM DUAL
UNION ALL SELECT 2006 , 1 , 'LCD' , 20 QTY FROM DUAL
UNION ALL SELECT 2006 , 2 , 'CRT' , 30 QTY FROM DUAL
UNION ALL SELECT 2006 , 2 , 'LCD' , 40 QTY FROM DUAL
UNION ALL SELECT 2007 , 1 , 'CRT' , 50 QTY FROM DUAL
UNION ALL SELECT 2007 , 1 , 'LCD' , 60 QTY FROM DUAL
UNION ALL SELECT 2007 , 2 , 'CRT' , 70 QTY FROM DUAL
UNION ALL SELECT 2007 , 2 , 'LCD' , 80 QTY FROM DUAL
)
[예상 결과]
YYYY MM ITEM QTY
---------- ---------- ---- ----------
2006 1 CRT 10
2006 1 LCD 20
2006 2 CRT 30
2006 2 LCD 40
2007 1 CRT 50
2007 1 LCD 60
2007 2 CRT 70
2007 2 LCD 80
CRT 160
LCD 200
10 rows selected
---------- ---------- ---- ----------
2006 1 CRT 10
2006 1 LCD 20
2006 2 CRT 30
2006 2 LCD 40
2007 1 CRT 50
2007 1 LCD 60
2007 2 CRT 70
2007 2 LCD 80
CRT 160
LCD 200
10 rows selected
[UNION 을 이용하는 방법]
SELECT YYYY, MM, ITEM, QTY
FROM T
UNION ALL
SELECT NULL, NULL, ITEM, SUM(QTY)
FROM T
GROUP BY ITEM
ORDER BY 1,2,3
FROM T
UNION ALL
SELECT NULL, NULL, ITEM, SUM(QTY)
FROM T
GROUP BY ITEM
ORDER BY 1,2,3
[그룹함수 ROLLUP 을 이용하는 경우]
SELECT YYYY, MM, ITEM, SUM(QTY)
FROM T
GROUP BY ROLLUP ((YYYY, MM)), ITEM
ORDER BY 1,2,3
FROM T
GROUP BY ROLLUP ((YYYY, MM)), ITEM
ORDER BY 1,2,3
[그룹함수 GROUPING SETS 을 이용하는 경우]
SELECT YYYY, MM, ITEM, SUM(QTY)
FROM T
GROUP BY GROUPING SETS ((YYYY, MM, ITEM), (ITEM))
ORDER BY 1,2,3
FROM T
GROUP BY GROUPING SETS ((YYYY, MM, ITEM), (ITEM))
ORDER BY 1,2,3
댓글을 달아 주세요