Update one column based on count of records of grouped value in another column
CREATE TABLE TABLE_ITEMS (
    serial_num int null,
    item_group int null
 )
 GO

INSERT INTO TABLE_ITEMS
VALUES 
(0,1), (1,1), (2,1),(3,1) ,(4,1) ,(0,2) ,(1,2), (2,2), (3,2), (4,2), (5,2), (0,3) ,(1,3) ,(2,3)
GO

SELECT 
    Item_group, 
    ROW_NUMBER() OVER (PARTITION BY item_group order by item_group) AS AddNum 
INTO #NewSerials
from TABLE_ITEMS 
WHERE serial_num &lt;&gt; 0

SELECT * FROM TABLE_ITEMS

;WITH CTE AS 
(SELECT MAX(addnum) as addnum, item_group from #NewSerials group by item_group)
UPDATE ti
    SET ti.serial_num = CTE.AddNum
FROM TABLE_ITEMS ti
JOIN CTE ON CTE.item_group = ti.item_group
WHERE ti.serial_num = 0

SELECT * FROM TABLE_ITEMS

5   1
1   1
2   1
3   1
4   1
6   2
1   2
2   2
3   2
4   2
5   2
3   3
1   3
2   3