CODEWITHSUNDEEP
sql-serversql-server-2008
som_1522
som_1522

Reputation: 55

Add the previous row value in commutative manner

I am stuck in this query. Table T1 is like:

Name ID
A    1 
A   -1
B    2
C    3
C    4
E   -1

I need output like:

Name ID   Output
A    1      1
A   -1      0
B    2      2 
C    3      5
C    4      9
E   -1      8

What I have tried:

SELECT t.NAME,
        cs.[Count(ID)]

FROM   T1 t
        CROSS apply (SELECT Sum([ID]) [Count(ID)]

                    FROM   T1  t1
                    WHERE  t1.ID <= t.ID ) cs

I am not getting the correct output because it is ordering by Id not name or may be any other issue.

Upvotes: 0

Views: 45

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use window function :

sum(id) over (order by name, case when id < 0 then 1 else 0 end, id)

You can also use apply :

select t1.*, t11.Output
from t1 t1 cross apply
     (select sum(t11.id) as Output
      from t1 t11
      where t11.? <= t.? -- Use ordering column instead.
     ) t11;

Upvotes: 1

Related Questions