Cumulating value of current row + sum of previous rows

Question

How would you do to transform a Column in a table from this:

ColumnA   ColumnB
2           a
3           b
4           c
5           d
1           a

to this:

ColumnA          ColumnB
3                 a
6(=3+3)           b   
10(=4+3+3)        c   
15(=5+4+3+3)      d 

I'm interested to see esp. what method you would pick.

Answer 1

Like this:

;WITH cte
AS
(
   SELECT ColumnB, SUM(ColumnA) asum 
   FROM @t 
   gROUP BY ColumnB

), cteRanked AS
(
   SELECT asum, ColumnB, ROW_NUMBER() OVER(ORDER BY ColumnB) rownum
   FROM cte
) 
SELECT (SELECT SUM(asum) FROM cteRanked c2 WHERE c2.rownum <= c1.rownum),
  ColumnB
FROM cteRanked c1;

This should give you:

ColumnA    ColumnB
3             a
6             b
10            c
15            d

---

Here is a live demo

Answer 2

I'd generally avoid trying to do so, but the following matches what you've asked for:

declare @T table (ColumnA int,ColumnB char(1))
insert into @T(ColumnA,ColumnB) values
(2    ,       'a'),
(3   ,        'b'),
(4  ,         'c'),
(5 ,          'd'),
(1,           'a')

;With Bs as (
    select distinct ColumnB from @T
)
select
    SUM(t.ColumnA),b.ColumnB
from
    Bs b
        inner join
    @T t
        on
            b.ColumnB >= t.ColumnB
group by
    b.ColumnB

Result:

            ColumnB
----------- -------
3           a
6           b
10          c
15          d

For small data sets, this will be fine. But for larger data sets, note that the last row of the table relies on obtaining the SUM over the entire contents of the original table.