Count how many times the data appear in a table and how to update it

1

Hello i have a problem, i have a field which name is “account” and its varchar type,this field have data that can repeat or can only apear once, i need that other field called “consecutive” counts how many times does the data in “account” apears, i can’t do it manually because i have lots of data.
This is an example of what i have
Account Consecutive
24590 1
24590 1
23789 1
23789 1
23789 1
98789 1
98789 1
89768 1
89768 1
15678 1
15678 1
14356 1

This is what i need
Account Consecutive
24590 1
24590 2
23789 1
23789 2
23789 3
98789 1

2

always provide sample ddl dml like this

create table #Ssantan(Account int,	Consecutive int)
insert into #Ssantan
select 24590,	1 union all
select 24590,	1 union all
select 23789,	1 union all
select 23789,	1 union all
select 23789,	1 union all
select 98789,	1 union all
select 98789,	1 union all
select 89768,	1 union all
select 89768,	1 union all
select 15678,	1 union all
select 15678,	1 union all
select 14356,	1

select Account, ROW_NUMBER() OVER(PARTITION BY Account ORDER BY Consecutive ASC) AS Consecutive
  From #Ssantan

drop table #Ssantan
3

ive got this so far guys

declare @no_cuenta NUMERIC;
declare @conta numeric
set @conta=0;
DECLARE c_consecutivo CURSOR FOR
SELECT no_cuenta
FROM [dbo].[BC_3259_dup_prueba]

OPEN c_consecutivo
FETCH NEXT FROM c_consecutivo INTO @no_cuenta
WHILE @@FETCH_STATUS = 0
BEGIN

			  UPDATE [dbo].[BC_3259_dup_prueba]
               SET consecutivo= CASE WHEN no_cuenta = @no_cuenta THEN @conta +1 else 2  END

FETCH NEXT FROM c_consecutivo INTO @no_cuenta

END -- De BEGIN
CLOSE c_consecutivo
DEALLOCATE c_consecutivo

4

What about this:

DECLARE @tbl table (Account int,	Consecutive int NOT NULL DEFAULT 1)
INSERT @tbl (Account)
VALUES
    (24590)
   ,(24590)
   ,(23789)
   ,(23789)
   ,(23789)
   ,(98789)
   ,(98789)
   ,(89768)
   ,(89768)
   ,(15678)
   ,(15678)
   ,(14356);

SELECT t.Account
     , t.Consecutive
FROM @tbl t;

SELECT 
     Count(t.Consecutive) [Count]
   , t.Account
FROM
   @tbl t
GROUP BY
   t.Account;

UPDATE t
SET t.Consecutive = agr.[Count]
FROM 
   @tbl t
JOIN 
   (
   SELECT 
        Count(t.Consecutive) [Count]
      , t.Account
   FROM
      @tbl t
   GROUP BY
      t.Account) agr 
ON t.Account=agr.Account;

SELECT t.Account
     , t.Consecutive
FROM @tbl t

image.png186×694 6.26 KB