chrisSpaceman chrisSpaceman - 3 months ago 7
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!

Answer

Use Row_Number and then partition by

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