today:
1,076
yesterday:
1,189
Total:
1,008,456

SQL Monthly SUM

admin 2023.05.22 20:03 Views : 43

DECLARE

 YEAR NUMBER;

 

BEGIN

DELETE FROM HISTORY_SUM;

INSERT INTO HISTORY_SUM (YEAR)

Select DISTINCT(TO_NUMBER(TO_CHAR(VISIT_DATE, 'YYYY'))) as YEAR from HISTORY

ORDER BY YEAR;

 

     FOR x IN (Select * from HISTORY_SUM) LOOP

     

     UPDATE HISTORY_SUM

     SET JAN = (Select SUM(TOTAL_$) from HISTORY c

WHERE TO_NUMBER(TO_CHAR(VISIT_DATE, 'YYYY')) = YEAR AND TO_NUMBER(TO_CHAR(VISIT_DATE, 'MM')) = 1)

     WHERE YEAR = x.YEAR;

     

     UPDATE HISTORY_SUM

     SET FEB = (Select SUM(TOTAL_$) from HISTORY c

WHERE TO_NUMBER(TO_CHAR(VISIT_DATE, 'YYYY')) = YEAR AND TO_NUMBER(TO_CHAR(VISIT_DATE, 'MM')) = 2)

     WHERE YEAR = x.YEAR;

     

     UPDATE HISTORY_SUM

     SET MAR = (Select SUM(TOTAL_$) from HISTORY c

WHERE TO_NUMBER(TO_CHAR(VISIT_DATE, 'YYYY')) = YEAR AND TO_NUMBER(TO_CHAR(VISIT_DATE, 'MM')) = 3)

     WHERE YEAR = x.YEAR;

     

     UPDATE HISTORY_SUM

     SET APR = (Select SUM(TOTAL_$) from HISTORY c

WHERE TO_NUMBER(TO_CHAR(VISIT_DATE, 'YYYY')) = YEAR AND TO_NUMBER(TO_CHAR(VISIT_DATE, 'MM')) = 4)

     WHERE YEAR = x.YEAR;     

     

     UPDATE HISTORY_SUM

     SET MAY = (Select SUM(TOTAL_$) from HISTORY c

WHERE TO_NUMBER(TO_CHAR(VISIT_DATE, 'YYYY')) = YEAR AND TO_NUMBER(TO_CHAR(VISIT_DATE, 'MM')) = 5)

     WHERE YEAR = x.YEAR;

     

     UPDATE HISTORY_SUM

     SET JUN = (Select SUM(TOTAL_$) from HISTORY c

WHERE TO_NUMBER(TO_CHAR(VISIT_DATE, 'YYYY')) = YEAR AND TO_NUMBER(TO_CHAR(VISIT_DATE, 'MM')) = 6)

     WHERE YEAR = x.YEAR;

     

     UPDATE HISTORY_SUM

     SET JUL = (Select SUM(TOTAL_$) from HISTORY c

WHERE TO_NUMBER(TO_CHAR(VISIT_DATE, 'YYYY')) = YEAR AND TO_NUMBER(TO_CHAR(VISIT_DATE, 'MM')) = 7)

     WHERE YEAR = x.YEAR;

     

     UPDATE HISTORY_SUM

     SET AUG = (Select SUM(TOTAL_$) from HISTORY c

WHERE TO_NUMBER(TO_CHAR(VISIT_DATE, 'YYYY')) = YEAR AND TO_NUMBER(TO_CHAR(VISIT_DATE, 'MM')) = 8)

     WHERE YEAR = x.YEAR;

     

     UPDATE HISTORY_SUM

     SET SEP = (Select SUM(TOTAL_$) from HISTORY c

WHERE TO_NUMBER(TO_CHAR(VISIT_DATE, 'YYYY')) = YEAR AND TO_NUMBER(TO_CHAR(VISIT_DATE, 'MM')) = 9)

     WHERE YEAR = x.YEAR;

     

     UPDATE HISTORY_SUM

     SET OCT = (Select SUM(TOTAL_$) from HISTORY c

WHERE TO_NUMBER(TO_CHAR(VISIT_DATE, 'YYYY')) = YEAR AND TO_NUMBER(TO_CHAR(VISIT_DATE, 'MM')) = 10)

     WHERE YEAR = x.YEAR;     

     

     UPDATE HISTORY_SUM

     SET NOV = (Select SUM(TOTAL_$) from HISTORY c

WHERE TO_NUMBER(TO_CHAR(VISIT_DATE, 'YYYY')) = YEAR AND TO_NUMBER(TO_CHAR(VISIT_DATE, 'MM')) = 11)

     WHERE YEAR = x.YEAR;

     

     UPDATE HISTORY_SUM

     SET DEC = (Select SUM(TOTAL_$) from HISTORY c

WHERE TO_NUMBER(TO_CHAR(VISIT_DATE, 'YYYY')) = YEAR AND TO_NUMBER(TO_CHAR(VISIT_DATE, 'MM')) = 12)

     WHERE YEAR = x.YEAR;

        

  END LOOP;    

 

COMMIT;

END;

No. Subject Author Date Views
» Monthly SUM admin 2023.05.22 43
40 LOOP TABLE UPDATE admin 2023.05.19 56
39 STARTUP admin 2022.09.01 86
38 EMPLOYEE_LEVEL_ID admin 2016.05.03 89
37 Display Value, Return Value admin 2016.05.10 117
36 Oracle SQL Developer: Online Migration 3.1 admin 2018.04.25 121
35 LIST OF VALUE (DYNAMIC) admin 2016.04.28 129
34 Global & Custom Serach file admin 2016.04.28 129
33 HEAD TITLE (제목)을 조건에 따라 바꾸기 admin 2016.05.03 135
32 How to Update Oracle XE admin 2018.04.25 141
31 Outer Join with Oracle SQL admin 2016.04.29 148
30 Field Showing admin 2016.04.01 149
29 COUNT admin 2016.04.28 149
28 CREATE (INSERT) PROCESS admin 2016.04.28 152
27 NVL admin 2016.05.19 152
26 PHONE NUMBER admin 2016.04.28 153
25 MAX admin 2016.04.28 155
24 DELETE PROCESS admin 2016.04.28 159
23 INSERT PROCESS admin 2016.04.28 160
22 SQL SELECT DISTINCT Statement admin 2016.04.29 162