CODEWITHSUNDEEP
sql-server
ashok kuna
ashok kuna

Reputation: 13

SQL Server: increment row number only one value

I have this table:

Id status
-----------
1   true
2   false
3   true
4   true
5   false

I need output like this:

Id status  count
-----------------
1   true    1
2   false   1
3   true    2
4   true    3
5   false   3

So I need to increment only true and but not false

Upvotes: 0

Views: 108

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

You can use the window functions ( well worth your time to get familiar with)

Example

Declare @YourTable Table ([Id] int,[status] varchar(50))  Insert Into @YourTable Values 
 (1,'true')
,(2,'false')
,(3,'true')
,(4,'true')
,(5,'false')
 
Select *
      ,count = sum(case when status='true' then 1 else 0 end) over (order by id) 
 From @YourTable

Returns

Id  status  count
1   true    1
2   false   1
3   true    2
4   true    3
5   false   3

Edit... If [status] is a bit

...
,count = sum(convert(int,status)) over (order by id) 
...

Upvotes: 2

Related Questions