CODEWITHSUNDEEP
sqloracle-database
Cimplicity
Cimplicity

Reputation: 2732

Oracle Rolling or Running totals by month

Goal: Rolling/Running total for all statements at the end of each month.

Code:

select 
    TRUNC(ACTHX.STMT_HX_STMT_DATE, 'MM') AS MNTH, 
    COUNT(ACTHX.INVOICE) as STMTS
from ACTHX
group by
    TRUNC(ACTHX.STMT_HX_STMT_DATE, 'MM')
ORDER BY
    TRUNC(ACTHX.STMT_HX_STMT_DATE, 'MM')

Current Output:

MNTH        STMTS
7/1/2009    1
10/1/2010   4
6/1/2011    26
9/1/2011    211
10/1/2011   163
11/1/2011   119

Desired output:

MNTH        STMTS
7/1/2009    1
10/1/2010   5
6/1/2011    31
9/1/2011    242
10/1/2011   405
11/1/2011   524

Upvotes: 8

Views: 8528

Answers (2)

Cimplicity
Cimplicity

Reputation: 2732

An alternative solution that provides the same rolling totals without the sub query. 

SELECT
        DISTINCT TRUNC(ACTHX.STMT_HX_STMT_DATE,'MM') AS MNTH
        ,count(ACTHX.INVOICE) OVER (ORDER BY TRUNC(ACTHX.STMT_HX_STMT_DATE,'MM')) AS STMTS
    FROM  ACTHX
    ORDER BY
        TRUNC(ACTHX.STMT_HX_STMT_DATE,'MM')

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231751

It sounds like you want the running total. You can use the analytic SUM function for that.

select mnth,
       sum(stmts) over (order by mnth) stmts
  from (select TRUNC(ACTHX.STMT_HX_STMT_DATE, 'MM') AS MNTH, 
               COUNT(ACTHX.INVOICE) as STMTS
          from ACTHX
         group by TRUNC(ACTHX.STMT_HX_STMT_DATE, 'MM'))
 ORDER BY mnth

Upvotes: 14

Related Questions