Increment variable in sql query

Question

Using SQL Server 2008, I want to query a table like so:

 | ID | Number 
 -------------
 |  1 |   0    
 |  2 |   0    
 |  3 |   1    
 |  4 |   0    
 |  5 |   0    
 |  6 |   1    
 |  7 |   1    
 |  8 |   1

The result should be the same table with an additional column that counts.

The method of counting is: if the number in "number" equals to 1 - increment the counter by one for the next line.

An example of result for the provided table:

 | ID | Number | Counter
 -----------------------
 |  1 |   0    |    1
 |  2 |   0    |    1
 |  3 |   1    |    1
 |  4 |   0    |    2
 |  5 |   0    |    2
 |  6 |   1    |    2
 |  7 |   1    |    3
 |  8 |   1    |    4

How can this be achieved?

I think that if you work on MySQL it is possible to do it with out subquery but in Sql Server i don't think so. — Hamidreza
– Hamidreza, Commented Apr 7, 2014 at 12:03

Prahalad Gaggar · Accepted Answer · 2014-04-07 11:40:11Z

3

select [ID], [Number],
       isnull(1+(select sum([Number]) from Table1 t2 where t2.ID<t1.Id),1)
from Table1 t1

SQL Fiddle to test

answered Apr 7, 2014 at 11:40

Prahalad Gaggar

11.6k17 gold badges58 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thorsten Kettner · Accepted Answer · 2014-04-07 11:42:31Z

1

This is not too hard to do. What you are looking for is very much like the running total, which you get with sum and a windowing clause.

select id, num, 1 + sum(num) over (order by id) - num as counter
from mytable
order by id;

Here is an SQL fiddle: http://sqlfiddle.com/#!4/958e2a/1.

answered Apr 7, 2014 at 11:42

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

5 Comments

Prahalad Gaggar Over a year ago

OP is using SQL Server

Itzik Gili Over a year ago

Is it faster then Luv's answer?

Thorsten Kettner Over a year ago

@Luv: The request was only tagged "sql" at first. Anyhow, SQL Server does support window functions. So it only depends on the version wether above query works or not.

Thorsten Kettner Over a year ago

@Itzik Gili: It should be faster. Ideally the table is read just once, which is likely to happen with SUM OVER. In Luv's statement the table must be read again and again.

Thorsten Kettner Over a year ago

Just checked it. SUM OVER is supported in SQL Server 2012, but not in SQL Server 2008.

Hamidreza · Accepted Answer · 2014-04-07 11:56:56Z

1

You can use recursive select too but it is a bit complicated but if you insert other numbers which are greater than 1 it work fine:

with tab(id,number,counter,rn) as
(select t.*,1 as counter,1 as rn from table1 t where id = 1
union all
select t.*,case when t.number = 1 then counter + 1 else counter end as counter,
rn + 1 as rn from table1 t,tab where t.id = tab.rn + 1),
tab2 as (select id,number,counter from tab)
select id,number,case when number = 1 then counter - 1 
else counter end as counter from tab2;

SQL Fiddle

edited Apr 7, 2014 at 11:56

answered Apr 7, 2014 at 11:49

Hamidreza

3,1581 gold badge20 silver badges15 bronze badges

Collectives™ on Stack Overflow

Increment variable in sql query

3 Answers 3

Comments

5 Comments

SQL Fiddle

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related