전월 당월 집계 비교데이타 입니다. 0 2 1,044

by lgxj20 [2018.11.28 15:02:32]


문의사항.png (30,604Bytes)

달월전월 비교 SQL입니다.

첨부된 결과 값처럼 나오게해야 되는데요 피벗쓰면 될거 같은데..될꺼 같으면서도 잘안되네요

by 마농 [2018.11.28 15:47:22]
WITH t AS
(
SELECT '201810' yymm, 1 tot_cnt FROM dual
UNION ALL SELECT '201811', 1 FROM dual
UNION ALL SELECT '201810', 1 FROM dual
UNION ALL SELECT '201811', 1 FROM dual
UNION ALL SELECT '201810', 1 FROM dual
UNION ALL SELECT '201810', 3 FROM dual
UNION ALL SELECT '201811', 1 FROM dual
UNION ALL SELECT '201811', 2 FROM dual
UNION ALL SELECT '201811', 2 FROM dual
UNION ALL SELECT '201811', 1 FROM dual
UNION ALL SELECT '201810', 2 FROM dual
UNION ALL SELECT '201811', 1 FROM dual
UNION ALL SELECT '201810', 2 FROM dual
UNION ALL SELECT '201810', 1 FROM dual
UNION ALL SELECT '201811', 1 FROM dual
UNION ALL SELECT '201810', 1 FROM dual
UNION ALL SELECT '201811', 3 FROM dual
)
-- Group By & Decode
SELECT LEAST(tot_cnt, 3) tot_cnt
     , COUNT(DECODE(yymm, '201810', 1)) "201810"
     , COUNT(DECODE(yymm, '201811', 1)) "201811"
  FROM t
 WHERE yymm IN ('201810', '201811')
 GROUP BY LEAST(tot_cnt, 3)
;
-- Pivot --
SELECT *
  FROM (SELECT yymm
             , LEAST(tot_cnt, 3) tot_cnt
          FROM t
         WHERE yymm IN ('201810', '201811')
        )
 PIVOT (COUNT(*) FOR yymm IN ('201810' "201810", '201811' "201811"))
;

 


by lgxj20 [2018.11.29 08:59:44]

간단하게 하는방법이있었네요..감사합니다...

항상 많이 배워갑니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입