Count Number of Consecutive Occurrence of values in Table
<p>I have below table </p>

<pre><code>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')
</code></pre>

<p>I want to count consecutive values in name column</p>

<pre><code>+------+------------+
| Name | Repetition |
+------+------------+
| A    |          2 |
| B    |          4 |
| C    |          1 |
| B    |          2 |
+------+------------+
</code></pre>

<p>The best thing I tried is:</p>

<pre><code>select Name
, COUNT(*) over (partition by Name order by Id) AS Repetition
from #t
order by Id
</code></pre>

<p>but it doesn't give me expected result</p>