I am trying to make a cumulative sum with the following query:
SELECT to_char(INVC_DT, 'MON-YYYY') AS MONTH
,SUM(INVC_AMT)
/*, aqui necesito una tercera columna con la suma cumulativa de la segunda columna*/
FROM T_INVC_INFO I INNER JOIN T_TASK_INFO T ON I.TASK_ID = T.TASK_ID
WHERE T.CNTRCT_ID = #session.user.cntrct_id#
GROUP BY to_char(INVC_DT, 'MON-YYYY')
ORDER BY MONTH DESC
In the end I solved it with this query with the help of StackOverflow in English:
SELECT to_char(INVC_DT, 'MON-YYYY') AS MONTH,
SUM(INVC_AMT) AS SUM_MONTH,
SUM(SUM(INVC_AMT)) OVER (ORDER BY MIN(INVC_DT)) AS CUMULATIVE_SUM
FROM T_INVC_INFO I INNER JOIN T_TASK_INFO T ON I.TASK_ID = T.TASK_ID
WHERE T.CNTRCT_ID = #session.user.cntrct_id#
GROUP BY to_char(INVC_DT, 'MON-YYYY')
ORDER BY MIN(INVC_DT)
You can try it this way:
set serveroutput on;
DECLARE
cursor cCOnsulta is select 'fecha1' fecha, 20 valor from dual union select 'fecha2' fecha, 30 valor from dual union select 'fecha3' fecha, 40 valor from dual;
registro cCOnsulta%ROWTYPE;
acumulador number := 0;
BEGIN
open cCOnsulta;
LOOP
FETCH cCOnsulta into registro;
EXIT WHEN cCOnsulta%notfound;
acumulador := acumulador + registro.valor;
//aqui podrias insertar los valores en una tabla temporal o imprimirlos
DBMS_OUTPUT.PUT_LINE(registro.fecha||' '||registro.valor||' '||acumulador);
END LOOP;
close cCOnsulta;
END;
I hope it serves you!