Grouping data with step down summation

Question

I have a table with OrderDate,TotalAmount. I want to display month and TotalAmount of month with total amount of previous month to be added in next month.

e.g.

OrderDate    TotalAmount  
----------   -----------  
13.01.1998---     10  
15.01.1998---     11  
01.02.1998---     12  
18.02.1998---     10  
12.03.1998---     09

Output should be

Month     TotalSum  
------    --------  
1---           21  
2---           43  
3---           52

When you ask a sql query question specify what RDBMS you're using (MySql, SQL Server, Oracle...) — peterm, Sep 08 '13 at 06:26
@AnandGupta check this question - http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver and my answer if performance matters for you — Roman Pekar, Sep 08 '13 at 07:44

score 3 · Accepted Answer · answered Sep 08 '13 at 07:00

If your data would only be from a single calendar year, you could use

with g as
( select month(orderdate) as ordermonth,
         sum( totalamount ) as sales
    from orders
    group by month(orderdate)
)
select m.ordermonth, sum(t.sales) as totalsales
  from g as m
  join g as t   on m.ordermonth >= t.ordermonth
  group by m.ordermonth
  order by m.ordermonth

But if there is ANY chance that your data could have two years, then you need year in there as well, so construct your month to include year.

with g as
( select format(orderdate, 'yyyy-MM') as ordermonth,
         sum( totalamount ) as sales
    from orders
    group by format(orderdate, 'yyyy-MM')
)
select m.ordermonth, sum(t.sales) as totalsales
  from g as m
  join g as t   on m.ordermonth >= t.ordermonth
  group by m.ordermonth
  order by m.ordermonth

Note: I work with DB2 for i, but instead gave you an answer that I believe should work on SQL Server. — WarrenT, Sep 08 '13 at 07:03

Grouping data with step down summation

1 Answers1