Cumulative sum for each different ID

Question

I have table something like this:

   TypeA   TypeSize
    110       2
    110       2
    110       6
    200       5
    200       7
    301       1
    301       2
    301       5
    301       1

And would like to sum up the size cumulative for each row, but only for the same Type. So I would get something like this:

   TypeA   TypeSize  Csize
    110       2        2
    110       2        4
    110       6        10
    200       5        5
    200       7        12
    301       1        1
    301       2        3
    301       5        8
    301       1        9

Cumulative sum is working great with:

SUM(CAST(TypeSize AS bigint)) OVER(ORDER BY TypeA ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Csize

So the problem is, that now I get the full cumulative sum of all of the Types. It does not look like the second output.

I have to GROUP BY TypeA somehow. But how and where - I thought first I need a for each (for each or iterate over my SUM select for each different Type)

    For Each TypeA:
    SELECT *,
    SUM(CAST(TypeSize AS bigint)) 
          OVER(ORDER BY TypeA ROWS BETWEEN 
          UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS Csize
    FROM Table

But for this short sql, do I really need a big for each like here posted?

Any suggestions? Thank you

Please check your expected results. Something doesn't look right about them compared to your explanation. For example, for 110 doesn't 2+2+6 = 10? — Tom H, Jan 13 '16 at 21:31
Also I think the Csize should be for TypeA = 200 and TypeSize = 7 as 7+5 = 12 — Fuzzy, Jan 13 '16 at 21:41
Yes you are right guys, I edited the results. Sorry it was late in the night :) — Lis, Jan 14 '16 at 07:22

score 3 · Accepted Answer · answered Jan 13 '16 at 21:29

Here is my solution:

You are almost there just need PARTITION BY

CREATE TABLE #temp (TypeA int ,  TypeSize int )

INSERT INTO  #temp (TypeA ,  TypeSize)
 VALUES (   110  ,     2),
   ( 110  ,     2),
   ( 110   ,    6),
   ( 200    ,   5),
   ( 200    ,   7),
   ( 301    ,   1),
   ( 301    ,   2),
   ( 301    ,   5),
   ( 301    ,   1)

   SELECT TypeA ,  TypeSize, SUM(CAST(TypeSize AS bigint)) OVER(PARTITION BY TypeA  ORDER BY TypeA ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Csize FROM #temp AS A

And the results:

Oh I can use there also a PARTITION BY. That's great thx, as I thought using GROUP BY FOR EACH would be overkill. — Lis, Jan 14 '16 at 07:23

score 0 · Answer 2 · answered Jan 14 '16 at 10:57

For those on 2005 and cant use rows unbounded preceeding and current row:

with cte
as
(
select *,row_number() over (partition by typea order by typea) as rn
from #temp
)
select typea,typesize,(select sum(typesize) from cte t1 where t1.rn<t2.rn+1 and t1.typea=t2.typea
group by typea
) as cszie
from cte t2

Cumulative sum for each different ID

2 Answers2