Count Number of Consecutive Occurrence of values in Table

Question

I have below table

create table #t (Id int, Name char)  insert into #t values (1, 'A'), (2, 'A'), (3, 'B'), (4, 'B'), (5, 'B'), (6, 'B'), (7, 'C'), (8, 'B'), (9, 'B') 

I want to count consecutive values in name column

+------+------------+ | Name | Repetition | +------+------------+ | A    |          2 | | B    |          4 | | C    |          1 | | B    |          2 | +------+------------+ 

The best thing I tried is:

select Name , COUNT(*) over (partition by Name order by Id) AS Repetition from #t order by Id 

but it doesn't give me expected result

Answer 1

One approach is the difference of row numbers:

select name, count(*)  from (select t.*,              (row_number() over (order by id) -               row_number() over (partition by name order by id)              ) as grp       from t      ) t group by grp, name; 

The logic is easiest to understand if you run the subquery and look at the values of each row number separately and then look at the difference.