CODEWITHSUNDEEP
sqlsql-servert-sql
Lis
Lis

Reputation: 33

Cumulative sum for each different ID

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

Upvotes: 2

Views: 454

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28938

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

Upvotes: 0

Fuzzy
Fuzzy

Reputation: 3810

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:

enter image description here

Upvotes: 3

Related Questions