집계함수에서 1월부터 12월 까지 표현하고 싶습니다. 0 3 2,019

by 겨울눈 [2013.11.05 15:02:28]


WITH T AS (
 SELECT '1' AS COUNT1 
   ,'201303' AS YMD
 FROM DUAL
 UNION ALL
 SELECT '2' AS COUNT1 
   ,'201304' AS YMD
 FROM DUAL
 UNION ALL
 SELECT '3' AS COUNT1 
  ,'201305' AS YMD
 FROM DUAL
 UNION ALL
 SELECT '4' AS COUNT1 
   ,'201306' AS YMD
 FROM DUAL
 UNION ALL
 SELECT '5' AS COUNT1 
   ,'201207' AS YMD
 FROM DUAL
 )
 SELECT SUM(COUNT1),YMD FROM T GROUP BY YMD ORDER BY YMD;
SUM  YMD
5	201207
1	201303
2	201304
3	201305
4	201306

이렇게 값이 나오는걸 
null 201201
null 201202
null 201203
null 201204
null 201205
null 201206
5 201207
....중략
null 201301
null 201302
1 201303
....

이런식으로 표현하고 싶은데 어떤식으로 하면 될까요?

by 우리집아찌 [2013.11.05 15:29:49]
-- 좀 깔끔치 못하게 나왔습니다.
WITH T AS ( 
 SELECT '1' AS COUNT1 
  ,'201303' AS YMD 
 FROM DUAL 
 UNION ALL
 SELECT '2' AS COUNT1 
  ,'201304' AS YMD 
 FROM DUAL 
 UNION ALL
 SELECT '3' AS COUNT1 
 ,'201305' AS YMD 
 FROM DUAL 
 UNION ALL
 SELECT '4' AS COUNT1 
  ,'201306' AS YMD 
 FROM DUAL 
 UNION ALL
 SELECT '5' AS COUNT1 
  ,'201207' AS YMD 
 FROM DUAL 
 ) , T2 AS (
 SELECT a.YMD || LPAD(b.lv,2,'0') YMD FROM
(SELECT DISTINCT SUBSTR(YMD,1,4) YMD FROM T ) a ,
(SELECT LEVEL lv FROM DUAL CONNECT BY LEVEL <= 12 ) b
)

SELECT a.CNT , b.YMD FROM
(SELECT SUM(COUNT1) CNT,YMD FROM T GROUP BY YMD ORDER BY YMD )a , T2 b
WHERE b.YMD = a.YMD(+)
ORDER BY b.YMD

by feelie [2013.11.05 16:01:22]

10g 라면 파티션 아웃터조인을 사용하심이...
http://scidb.tistory.com/ -

New Features 의 중요성(Partition Outer Join 의 사용법)


by 겨울눈 [2013.11.05 16:01:48]
감사합니다 해결 했습니다
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입