chrisSpaceman chrisSpaceman - 1 year ago 76
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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download