chrisSpaceman chrisSpaceman - 7 months ago 30
SQL Question

Label number of occurence of value in column

I have the following table, tempTable:

Id | Type
01 L
23 D
45 L
67 L
89 L

I'd like to be able to add a new column which incrementally counts how many of each Type of row has appeared in the table above it. In the example above, I'd like the output to look like this:

Id | Type | Type Count
01 L L 1
23 D D 1
45 L L 2
67 L L 3
89 L L 4

Any help would be appreciated!


Use Row_Number and then partition by

[Type]+ ' '+CAST(Row_Number() OVER (partition by [Type] order by id) AS VARCHAR(5)) as 'Type Count'
Order by Id