sql - a column that increments for every row where column A = number and resets to zero when A = another number and increments again

Question

I need a column (LineCount) that increments for every instance of an ID in another column (ItemID) for example:

 |ItemID|LineCount|
 ------------------
 | 1    | 1
 ------------------
 | 1    | 2
 ------------------
 | 1    | 3
 ------------------
 | 2    | 1  
  ------------------
 | 2    | 2
 ------------------
 | 2    | 3
 ------------------
 | 2    | 4
 ------------------
 | 2    | 5
  ------------------
 | 3    | 1
 ------------------
 | 3    | 2
  ------------------
 | 3    | 3
 ------------------
 | 3    | 4

SQL may look something like:

"Insert into TableA increment LineCount Where ITEMID = @ITEMID"

Answer 1

Your code looks like SQL Server's. If so, you can use window function row_number to generate partitioned sequence numbers.

insert into tablea
select itemId,
    row_number() over (
        partition by itemId order by itemId
        )
from your_table;