CODEWITHSUNDEEP
sqloracle-databaseselectsum
alanfcm
alanfcm

Reputation: 663

Cumulative sum with expression in oracle

I am trying to get a cumulative sum with the following query:

SELECT to_char(INVC_DT, 'MON-YYYY') AS MONTH 
       ,SUM(INVC_AMT)
       /*, here I need a third column with the cumulative sum of the second column*/
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

Upvotes: 0

Views: 131

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I would just do:

SELECT to_char(INVC_DT, 'MON-YYYY') AS MONTH,
       SUM(INVC_AMT),
       SUM(SUM(INVC_AMT)) OVER (ORDER BY MIN(INVC_DT))
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;

Note the ORDER BY clause is by date rather than alphabetically by month.

You may also want ORDER BY MIN(INVC_DT) for the final order by as well.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311163

I'd wrap this query with another query and use the window variant of sum:

SELECT   month, sum_month, SUM(sum_month) OVER (ORDER BY month DESC)
FROM     (SELECT     TO_CHAR(invc_dt, 'MON-YYYY') AS month,
                     SUM(invc_amt) AS sum_month
          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

Upvotes: 1

Related Questions