sqlsql-servert-sqlsql-server-2012aggregation

Count Number of Consecutive Occurrence of values in Table


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


Solution

  • 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.