I'm looking to add some form of count function to my table, but am not quite sure how to do it. The table I have is:

I would like it to have a count, of the full names that come up twice or more, like so:

What is the best way to go about this in SQL? Unfortunately I need the result as per the table above, rather than simply:

CodePudding user response：

row_number function should work

select *,
row_number() over(partition by [first name],surname order by [first name]) as count 
from table_name

CodePudding user response：

I believe using group by on multiple columns would be a more appropriate approach to

select [first name], surname, COUNT(*) from Employee Group BY [first name], surname;

I believe using group by on multiple columns would be a more appropriate approach to

select [first name], surname, COUNT(*) from Employee Group BY [first name], surname;

Result: