today:
573
yesterday:
793
Total:
1,014,776

SQL Monthly SUM

admin 2023.05.22 20:03 Views : 52

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 52
40 LOOP TABLE UPDATE admin 2023.05.19 63
39 STARTUP admin 2022.09.01 93
38 ACTION admin 2019.01.11 6467
37 SELECT last_name FROM admin 2018.12.12 757
36 CREATE TABLE admin 2018.09.25 482
35 VERY IMPORTANT admin 2018.06.26 239
34 MISSIONARY_SUB admin 2018.06.25 213
33 TABLE PRINT admin 2018.06.18 201
32 GP PRINT admin 2018.05.31 928
31 How to convert DATE to NUMBER admin 2018.05.27 206
30 DATE Compare admin 2018.05.22 378
29 Oracle SQL Developer: Online Migration 3.1 admin 2018.04.25 131
28 How to Update Oracle XE admin 2018.04.25 151
27 SQL admin 2016.07.15 403
26 SUBSTR admin 2016.05.26 280
25 FOR LOOP admin 2016.05.20 278
24 ROWNUM admin 2016.05.19 246
23 NVL admin 2016.05.19 159
22 INSTR admin 2016.05.19 794