CODEWITHSUNDEEP
sqlsql-server
qzx
qzx

Reputation: 177

SQL: Increment a row when value in another row changes

I have the following table:

Sequence   Change
 100         0
 101         0
 103         0
 106         0
 107         1
 110         0
 112         1
 114         0
 115         0
 121         0
 126         1
 127         0
 134         0

I need an additional column, Group, whose values increment based on the occurrence of 1 in Change. How is that done? I'm using Microsoft Server 2012.

Sequence   Change    Group
 100         0         0
 101         0         0
 103         0         0
 106         0         0
 107         1         1
 110         0         1
 112         1         2
 114         0         2
 115         0         2
 121         0         2
 126         1         3
 127         0         3
 134         0         3

Upvotes: 1

Views: 321

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You want a cumulative sum:

select t.*, sum(change) over (order by sequence) as grp
from t;

Upvotes: 2

Related Questions