CODEWITHSUNDEEP
sqlsql-server
I.m.rich
I.m.rich

Reputation: 55

Summing Value Given Unique ID and Conditional date in SQL

I want to sum value, but only given unique ID and conditional date. I want all the values for an ID before the date of the row summed for example, my table looks like:

ID|Date     |Val| Val2         
x |1/1/2016|1|0           
y |1/1/2016|3|0  
z |1/1/2016|2|0  
x |2/1/2016|2|0  
y |2/1/2016|0|0  
z |2/1/2016|1|0  
x |3/1/2016|0|0  
y |3/1/2016|5|0   
z |3/1/2016|2|0

I want it to look like:

ID|Date     |Val| Val2         
x |1/1/2016|1|1           
y |1/1/2016|3|3  
z |1/1/2016|2|2  
x |2/1/2016|2|3  
y |2/1/2016|0|3  
z |2/1/2016|1|3  
x |3/1/2016|0|3  
y |3/1/2016|5|8   
z |3/1/2016|2|5

Any ideas? summing across columns is simple, and summing rows on unique ID by group by is logical, but how do you do a conditional sum across rows using the date and an ID?

Upvotes: 0

Views: 33

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

You are looking for a cumulative sum:

select id, date, val,
       sum(val) over (partition by id order by date) as val2
from t;

Upvotes: 1

Related Questions