CODEWITHSUNDEEP
sqlsql-serverdatabase
user1294510
user1294510

Reputation: 429

SQL Sum and grouping with condition

I have a table with sequences, and counts for each item. I'm trying to come up with a query that adds an extra column in the result, such that in the new column each row/item will have the sum of all the items' counts whose sequence is lower than the item.

e.g.:

items   [...]   seq    counts
a       [...]    1       1
b       [...]    2       1
c       [...]    3       8
d       [...]    4       2
a       [...]    1       1
e       [...]   null     1

result:

items   [...]   seq    counts  sum
a       [...]    1       1   (doesnt matter, could be 0 or 1)
b       [...]    2       1      1
c       [...]    3       8      2
d       [...]    4       2     10
a       [...]    1       1   (doesnt matter, could be 0 or 1)
e       [...]   null     1   (doesnt matter)
f       [...]    5       10    12

I dont know how to sum up something with a condition like this. I only know how to do group by and sum all the item in a group. Subquery isnt working for me either

Upvotes: 0

Views: 250

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You are looking for a cumulative sum. You can do this using outer apply:

select t.*, t2.sumcounts
from t outer apply
     (select sum(t2.counts) as sumcounts
      from t t2
      where t2.seq < t.seq
     ) t2;

Upvotes: 3

Related Questions