CODEWITHSUNDEEP
sqlsql-servert-sqlcountcounter
Abeer Sul
Abeer Sul

Reputation: 984

How to create this special counter on a specific value in T-SQL?

I've problem that I've been struggling with for hours. I'm trying to create a special counter on a column from a temporary table, my table has many columns, one of them is col1:

col1 |
######
X    | 
X    | 
A    | 
B    | 
X    | 
C    | 
X    | 
D    |

The special counter should be like this:

col1 | counter
###############
X    | 1
X    | 2
A    | 2
B    | 2
X    | 3
C    | 3
X    | 4
D    | 4

it should count the "X" value only, and keep it unchanged in case the value was anything else.

I tried a lot of things, the closest I got was by creating another temp table with counter only, and then join it with the original one, but the result was like this:

col1 | counter
###############
X    | 1
X    | 2
A    | NULL
B    | NULL
X    | 3
C    | NULL
X    | 4
D    | NULL

So, how can I create this special counter?

Upvotes: 4

Views: 6265

Answers (3)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

There is much simpler and streightforward solution. We just need a little observation: the counter equals to count of Xs in previous rows:

select id, 
       col1,
       (select count(*) from @t where id <= t.id and col1 = 'X') as counter
from @t t

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81990

Row_Number() within a CASE should do the trick.

You'll notice I added a field ID to maintain a sequence. 

Declare @YourTable table (ID int,col1 varchar(25))
Insert Into @YourTable values
(1,'X'),
(2,'X'),
(3,'A'),
(4,'B'),
(5,'X'),
(6,'C'),
(7,'X'),
(8,'D')

Select ID,Col1
      ,Counter = max(counter) over (Order By ID)
 From (
       Select ID
             ,col1
             ,counter = case when col1='X' then row_number() over (Partition By col1 Order by ID) else null end
        From  @YourTable
      ) A
 Order By ID

Returns

ID  Col1    Counter
1   X       1
2   X       2
3   A       2
4   B       2
5   X       3
6   C       3
7   X       4
8   D       4

Upvotes: 4

A.J. Schroeder
A.J. Schroeder

Reputation: 411

Here it is in an update statement. The statement increments the variable every time an X is encountered and updates every row with the correct value.

declare @counter int = 0;
update   #temp
set      counter = @counter
       , @counter += case when col1 = 'X' then 1
                          else 0
                     end;

Upvotes: 4

Related Questions