오라클 일별 통계

오라클 SH 스키마 필요

 

SELECT
    CUST_ID, YYYYMM, 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("13") AS "13일", SUM("14") AS "14일", SUM("15") AS "15일", SUM("16") AS "16일", SUM("17") AS "17일", SUM("18") AS "18일",
    SUM("19") AS "19일", SUM("20") AS "20일", SUM("21") AS "21일", SUM("22") AS "22일", SUM("23") AS "23일", SUM("24") AS "24일",
    SUM("25") AS "25일", SUM("26") AS "26일", SUM("27") AS "27일", SUM("28") AS "28일", SUM("29") AS "29일", SUM("30") AS "30일", SUM("31") AS "31일",
    SUM(AMOUNT_SOLD) AS "총계"
FROM(
        SELECT CUST_ID, TO_CHAR(TIME_ID,'YYYY-MM') AS YYYYMM, TO_CHAR(TIME_ID,'DD') AS DD, AMOUNT_SOLD
        , DECODE(TO_CHAR(TIME_ID,'DD'), '01', AMOUNT_SOLD, 0) AS "01"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '02', AMOUNT_SOLD, 0) AS "02"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '03', AMOUNT_SOLD, 0) AS "03"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '04', AMOUNT_SOLD, 0) AS "04"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '05', AMOUNT_SOLD, 0) AS "05"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '06', AMOUNT_SOLD, 0) AS "06"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '07', AMOUNT_SOLD, 0) AS "07"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '08', AMOUNT_SOLD, 0) AS "08"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '09', AMOUNT_SOLD, 0) AS "09"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '10', AMOUNT_SOLD, 0) AS "10"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '11', AMOUNT_SOLD, 0) AS "11"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '12', AMOUNT_SOLD, 0) AS "12"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '13', AMOUNT_SOLD, 0) AS "13"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '14', AMOUNT_SOLD, 0) AS "14"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '15', AMOUNT_SOLD, 0) AS "15"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '16', AMOUNT_SOLD, 0) AS "16"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '17', AMOUNT_SOLD, 0) AS "17"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '18', AMOUNT_SOLD, 0) AS "18"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '19', AMOUNT_SOLD, 0) AS "19"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '20', AMOUNT_SOLD, 0) AS "20"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '21', AMOUNT_SOLD, 0) AS "21"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '22', AMOUNT_SOLD, 0) AS "22"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '23', AMOUNT_SOLD, 0) AS "23"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '24', AMOUNT_SOLD, 0) AS "24"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '25', AMOUNT_SOLD, 0) AS "25"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '26', AMOUNT_SOLD, 0) AS "26"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '27', AMOUNT_SOLD, 0) AS "27"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '28', AMOUNT_SOLD, 0) AS "28"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '29', AMOUNT_SOLD, 0) AS "29"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '30', AMOUNT_SOLD, 0) AS "30"
        , DECODE(TO_CHAR(TIME_ID,'DD'), '31', AMOUNT_SOLD, 0) AS "31"
        FROM SH.SALES
        --WHERE TO_CHAR(TIME_ID,'YYYYMM') = '199801'
)GROUP BY CUST_ID, YYYYMM
ORDER BY CUST_ID ASC, YYYYMM ASC

 

 

결과