CODEWITHSUNDEEP
sql
chrisSpaceman
chrisSpaceman

Reputation: 249

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!

Upvotes: 2

Views: 46

Answers (2)

Drez
Drez

Reputation: 498

Solution without row_number():

SELECT tn.Type, tn.Type || '-' || 
(SELECT  1+COUNT(Type) FROM TableName 
   WHERE id < tn.id AND Type = tn.Type) AS number_of_occurrences 
FROM TableName tn

Upvotes: 0

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14679

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

Upvotes: 2

Related Questions