WITH T AS
(
SELECT '201209' YEARMON, 'UK' COUNTRY, 'WM' PRODUCT, 'F1' MODEL FROM DUAL
UNION ALL
SELECT '201209' YEARMON, 'UK' COUNTRY, 'WM' PRODUCT, 'F1' MODEL FROM DUAL
UNION ALL
SELECT '201209' YEARMON, 'UK' COUNTRY, 'WM' PRODUCT, 'F2' MODEL FROM DUAL
UNION ALL
SELECT '201209' YEARMON, 'KO' COUNTRY, 'WM' PRODUCT, 'F2' MODEL FROM DUAL
UNION ALL
SELECT '201209' YEARMON, 'UK' COUNTRY, 'WM' PRODUCT, 'F3' MODEL FROM DUAL
UNION ALL
SELECT '201209' YEARMON, 'UK' COUNTRY, 'RF' PRODUCT, 'F1' MODEL FROM DUAL
UNION ALL
SELECT '201209' YEARMON, 'UK' COUNTRY, 'RF' PRODUCT, 'F2' MODEL FROM DUAL
UNION ALL
SELECT '201209' YEARMON, 'UK' COUNTRY, 'RF' PRODUCT, 'F2' MODEL FROM DUAL
UNION ALL
SELECT '201209' YEARMON, 'KO' COUNTRY, 'RF' PRODUCT, 'F2' MODEL FROM DUAL
UNION ALL
SELECT '201209' YEARMON, 'UK' COUNTRY, 'RF' PRODUCT, 'F3' MODEL FROM DUAL
UNION ALL
SELECT '201209' YEARMON, 'KO' COUNTRY, 'RF' PRODUCT, 'F3' MODEL FROM DUAL
UNION ALL
SELECT '201209' YEARMON, 'UK' COUNTRY, 'RF' PRODUCT, 'F3' MODEL FROM DUAL
)
SELECT YEARMON, COUNTRY, SUM(RF_CNT), SUM(WM_CNT)
FROM(
SELECT YEARMON, COUNTRY, PRODUCT, 0 RF_CNT, COUNT(MODEL) WM_CNT
FROM T
WHERE PRODUCT = 'WM'
GROUP BY YEARMON, COUNTRY, PRODUCT
UNION ALL
SELECT YEARMON, COUNTRY, PRODUCT, COUNT(MODEL) RF_CNT, 0 WM_CNT
FROM T
WHERE PRODUCT = 'RF'
GROUP BY YEARMON, COUNTRY, PRODUCT
)
GROUP BY YEARMON, COUNTRY
다음과 같은 쿼리를 수행했을때 나오는 결과값은 제가 원하는 결과값이 맞습니다만..
중요한건 저런 식의 질의문은 제가 만들었지만 그닥 좋은 표현은 아닌듯 싶습니다.
혹, 고수님들께서 보시고 좋은 표현 방법이나 저런 상황에서 효율적으로 작성할 수 있는 질의문의 노하우를 전수받고 싶습니다.
부탁드리겠습니다.
감사합니다..
|