[오라클] 월별 통계

SH 스키마 필요 // 년도별 월별 통계 //2014.10.29 수정

 

SELECT
    CUST_ID, YYYY, SUM("01") AS "1월", SUM("02") AS "2월", SUM("03") AS "3월", SUM("04") AS "4월", SUM("05") AS "5월", SUM("06") AS "6월",
    SUM("07") AS "7월", SUM("08") AS "8월", SUM("09") AS "9월", SUM("10") AS "10월", SUM("11") AS "11월", SUM("12") AS "12월",
    SUM(AMOUNT_SOLD) AS "총계"
FROM(
        SELECT CUST_ID, TO_CHAR(TIME_ID,'YYYY') AS YYYY, TO_CHAR(TIME_ID,'MM') AS MM, TO_CHAR(TIME_ID,'DD') AS DD, AMOUNT_SOLD
        , DECODE(TO_CHAR(TIME_ID,'MM'), '01', AMOUNT_SOLD, 0) AS "01"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '02', AMOUNT_SOLD, 0) AS "02"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '03', AMOUNT_SOLD, 0) AS "03"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '04', AMOUNT_SOLD, 0) AS "04"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '05', AMOUNT_SOLD, 0) AS "05"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '06', AMOUNT_SOLD, 0) AS "06"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '07', AMOUNT_SOLD, 0) AS "07"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '08', AMOUNT_SOLD, 0) AS "08"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '09', AMOUNT_SOLD, 0) AS "09"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '10', AMOUNT_SOLD, 0) AS "10"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '11', AMOUNT_SOLD, 0) AS "11"
        , DECODE(TO_CHAR(TIME_ID,'MM'), '12', AMOUNT_SOLD, 0) AS "12"
        FROM SH.SALES
--        WHERE TO_CHAR(TIME_ID,'YYYY') BETWEEN 1999 AND 2000 --특정년도 범위

--        AND TO_CHAR(TIME_ID,'YYYYMM') = '199801'  --특정년월
--        AND TO_CHAR(TIME_ID,'MM') = 05 --특정달만
)GROUP BY CUST_ID, YYYY
ORDER BY CUST_ID ASC, YYYY ASC

 

결과