평균의 의미가 뭔가요?
  13개월치 월별 count 에 대한 전체 평균인가요?
  왜? 한줄 추가해서 똑같은 값을 13번이나 보여줘야 하죠?
  그냥 컬럼 하나 추가해서 한번만 보여주면 될 것 같은데요?

네 13개월치의 각각의 count값이 있고 그값들의 평균값을 구하려고 합니다..

이게 한줄을 더뿌려야하는 이유가 해당 조회값을 레포팅툴에 같이 연동하여 사용하려고 하다보니

한줄을 더 출력하는 방식으로해야 정상적으로 출력이 되어 한줄을 더 추가하려고 구성중입니다..

WITH tbl1 AS
(
-- test sample --
SELECT '20161201' acc_ymd FROM dual
UNION ALL SELECT '20170101' FROM dual
UNION ALL SELECT '20170201' FROM dual
UNION ALL SELECT '20170301' FROM dual
UNION ALL SELECT '20170401' FROM dual
UNION ALL SELECT '20170501' FROM dual
UNION ALL SELECT '20170601' FROM dual
UNION ALL SELECT '20170701' FROM dual
UNION ALL SELECT '20170801' FROM dual
UNION ALL SELECT '20170901' FROM dual
UNION ALL SELECT '20171001' FROM dual
UNION ALL SELECT '20171101' FROM dual
UNION ALL SELECT '20171201' FROM dual
UNION ALL SELECT '20171201' FROM dual
UNION ALL SELECT '20171201' FROM dual
UNION ALL SELECT '20180101' FROM dual
UNION ALL SELECT '20180201' FROM dual
)
SELECT gb
     , MIN(DECODE(mm, 01, ym)) mm01
     , MIN(DECODE(mm, 02, ym)) mm02
     , MIN(DECODE(mm, 03, ym)) mm03
     , MIN(DECODE(mm, 04, ym)) mm04
     , MIN(DECODE(mm, 05, ym)) mm05
     , MIN(DECODE(mm, 06, ym)) mm06
     , MIN(DECODE(mm, 07, ym)) mm07
     , MIN(DECODE(mm, 08, ym)) mm08
     , MIN(DECODE(mm, 09, ym)) mm09
     , MIN(DECODE(mm, 10, ym)) mm10
     , MIN(DECODE(mm, 11, ym)) mm11
     , MIN(DECODE(mm, 12, ym)) mm12
     , MIN(DECODE(mm, 13, ym)) mm13
     , MIN(DECODE(mm, 01, vv)) vv01
     , MIN(DECODE(mm, 02, vv)) vv02
     , MIN(DECODE(mm, 03, vv)) vv03
     , MIN(DECODE(mm, 04, vv)) vv04
     , MIN(DECODE(mm, 05, vv)) vv05
     , MIN(DECODE(mm, 06, vv)) vv06
     , MIN(DECODE(mm, 07, vv)) vv07
     , MIN(DECODE(mm, 08, vv)) vv08
     , MIN(DECODE(mm, 09, vv)) vv09
     , MIN(DECODE(mm, 10, vv)) vv10
     , MIN(DECODE(mm, 11, vv)) vv11
     , MIN(DECODE(mm, 12, vv)) vv12
     , MIN(DECODE(mm, 13, vv)) vv13
  FROM (SELECT mm
             , CASE WHEN mm = 1 OR m = '1' THEN y END || m || '월' ym
             , COUNT(*) cnt
             , ROUND(AVG(COUNT(*)) OVER()) avg
          FROM (SELECT SUBSTR(acc_ymd, 3, 2) || '.' y
                     , LTRIM(SUBSTR(acc_ymd, 5, 2), '0') m
                     , 13 - MONTHS_BETWEEN(TO_DATE('201801', 'yyyymm')
                            , TO_DATE(SUBSTR(acc_ymd, 1, 6), 'yyyymm')) mm
                  FROM tbl1
                 WHERE acc_ymd >= ('201801' - 100) || '01'
                   AND acc_ymd <= '201801'         || '31'
                )
         GROUP BY mm, y, m
        )
 UNPIVOT (vv FOR gb IN (cnt AS '합', avg AS '평균'))
 GROUP BY gb
;

마농님 답변 감사드립니다.

그런데 oracle 10g라 pivot 기능이 없네요..

혹시 괜찮으시면 10g용으로 하나만 더 부탁드려도 될까요..? ㅠㅠ

SELECT gb
     , MIN(DECODE(mm, 01, ym)) mm01
     , MIN(DECODE(mm, 02, ym)) mm02
     , MIN(DECODE(mm, 03, ym)) mm03
     , MIN(DECODE(mm, 04, ym)) mm04
     , MIN(DECODE(mm, 05, ym)) mm05
     , MIN(DECODE(mm, 06, ym)) mm06
     , MIN(DECODE(mm, 07, ym)) mm07
     , MIN(DECODE(mm, 08, ym)) mm08
     , MIN(DECODE(mm, 09, ym)) mm09
     , MIN(DECODE(mm, 10, ym)) mm10
     , MIN(DECODE(mm, 11, ym)) mm11
     , MIN(DECODE(mm, 12, ym)) mm12
     , MIN(DECODE(mm, 13, ym)) mm13
     , MIN(DECODE(mm, 01, vv)) vv01
     , MIN(DECODE(mm, 02, vv)) vv02
     , MIN(DECODE(mm, 03, vv)) vv03
     , MIN(DECODE(mm, 04, vv)) vv04
     , MIN(DECODE(mm, 05, vv)) vv05
     , MIN(DECODE(mm, 06, vv)) vv06
     , MIN(DECODE(mm, 07, vv)) vv07
     , MIN(DECODE(mm, 08, vv)) vv08
     , MIN(DECODE(mm, 09, vv)) vv09
     , MIN(DECODE(mm, 10, vv)) vv10
     , MIN(DECODE(mm, 11, vv)) vv11
     , MIN(DECODE(mm, 12, vv)) vv12
     , MIN(DECODE(mm, 13, vv)) vv13
  FROM (SELECT mm
             , CASE WHEN mm = 1 OR m = '1' THEN y END || m || '월' ym
             , DECODE(GROUPING(1), 0, '합', '평균') gb
             , DECODE(GROUPING(1)
               , 0, COUNT(*)
               , 1, ROUND(AVG(COUNT(*)) OVER())
               ) vv
          FROM (SELECT SUBSTR(acc_ymd, 3, 2) || '.' y
                     , LTRIM(SUBSTR(acc_ymd, 5, 2), '0') m
                     , 13 - MONTHS_BETWEEN(TO_DATE('201801', 'yyyymm')
                            , TO_DATE(SUBSTR(acc_ymd, 1, 6), 'yyyymm')) mm
                  FROM tbl1
                 WHERE acc_ymd >= ('201801' - 100) || '01'
                   AND acc_ymd <= '201801'         || '31'
                )
         GROUP BY mm, y, m, ROLLUP(1)
        )
 GROUP BY gb
;

감사합니다 마농님

덕분에 완성하였습니다!

확인이 늦었지만 너무너무 감사드립니다!