Q. 다음과 같이 LCD 매출 실적이 있을 경우
2005, 2006년 월별 각 아이템의 매출 실적 및 아이템별 매출 합계를 조회하는 쿼리를 작성하라.
WITH t AS
(
SELECT 2005 YYYY, 1 MM, 'LCD15' ITEM, 100 SALES FROM DUAL
UNION ALL SELECT 2005 , 1 , 'LCD17' , 200 FROM DUAL
UNION ALL SELECT 2005 , 1 , 'LCD19' , 250 FROM DUAL
UNION ALL SELECT 2005 , 2 , 'LCD15' , 90 FROM DUAL
UNION ALL SELECT 2005 , 2 , 'LCD17' , 170 FROM DUAL
UNION ALL SELECT 2005 , 2 , 'LCD19' , 240 FROM DUAL
UNION ALL SELECT 2006 , 1 , 'LCD15' , 60 FROM DUAL
UNION ALL SELECT 2006 , 1 , 'LCD17' , 160 FROM DUAL
UNION ALL SELECT 2006 , 1 , 'LCD19' , 310 FROM DUAL
UNION ALL SELECT 2006 , 2 , 'LCD15' , 40 FROM DUAL
UNION ALL SELECT 2006 , 2 , 'LCD17' , 130 FROM DUAL
UNION ALL SELECT 2006 , 2 , 'LCD19' , 340 FROM DUAL
)
(
SELECT 2005 YYYY, 1 MM, 'LCD15' ITEM, 100 SALES FROM DUAL
UNION ALL SELECT 2005 , 1 , 'LCD17' , 200 FROM DUAL
UNION ALL SELECT 2005 , 1 , 'LCD19' , 250 FROM DUAL
UNION ALL SELECT 2005 , 2 , 'LCD15' , 90 FROM DUAL
UNION ALL SELECT 2005 , 2 , 'LCD17' , 170 FROM DUAL
UNION ALL SELECT 2005 , 2 , 'LCD19' , 240 FROM DUAL
UNION ALL SELECT 2006 , 1 , 'LCD15' , 60 FROM DUAL
UNION ALL SELECT 2006 , 1 , 'LCD17' , 160 FROM DUAL
UNION ALL SELECT 2006 , 1 , 'LCD19' , 310 FROM DUAL
UNION ALL SELECT 2006 , 2 , 'LCD15' , 40 FROM DUAL
UNION ALL SELECT 2006 , 2 , 'LCD17' , 130 FROM DUAL
UNION ALL SELECT 2006 , 2 , 'LCD19' , 340 FROM DUAL
)
[결과]
YYYY MM ITEM SALES
---------- ---------- ----- ----------
2005 1 LCD15 100
2005 1 LCD17 200
2005 1 LCD19 250
2005 2 LCD15 90
2005 2 LCD17 170
2005 2 LCD19 240
2006 1 LCD15 60
2006 1 LCD17 160
2006 1 LCD19 310
2006 2 LCD15 40
2006 2 LCD17 130
2006 2 LCD19 340
LCD15 290
LCD17 660
LCD19 1140
---------- ---------- ----- ----------
2005 1 LCD15 100
2005 1 LCD17 200
2005 1 LCD19 250
2005 2 LCD15 90
2005 2 LCD17 170
2005 2 LCD19 240
2006 1 LCD15 60
2006 1 LCD17 160
2006 1 LCD19 310
2006 2 LCD15 40
2006 2 LCD17 130
2006 2 LCD19 340
LCD15 290
LCD17 660
LCD19 1140
[분석용함수 ROLLUP()을 이용하는 경우]
SELECT YYYY, MM, ITEM, SUM(SALES) SALES
FROM T
WHERE YYYY BETWEEN 2005 AND 2006
GROUP BY ROLLUP((YYYY, MM)), ITEM
ORDER BY 1,2,3
FROM T
WHERE YYYY BETWEEN 2005 AND 2006
GROUP BY ROLLUP((YYYY, MM)), ITEM
ORDER BY 1,2,3
[GROUPING SETS()를 이용하는 경우]
SELECT YYYY, MM, ITEM, SUM(SALES) SALES
FROM T
WHERE YYYY BETWEEN 2005 AND 2006
GROUP BY GROUPING SETS ((YYYY, MM, ITEM), (ITEM))
ORDER BY 1,2,3
FROM T
WHERE YYYY BETWEEN 2005 AND 2006
GROUP BY GROUPING SETS ((YYYY, MM, ITEM), (ITEM))
ORDER BY 1,2,3
댓글을 달아 주세요