count without group

Question

I have one table named GUYS(ID,NAME,PHONE) and i need to add a count of how many guys have the same name and at the same time show all of them so i can't group them. example:

ID NAME  PHONE
1  John  335   
2  Harry 444
3  James 367
4  John  742
5  John  654

the wanted output should be

ID NAME  PHONE COUNT
1  John  335   3
2  Harry 444   1
3  James 367   1
4  John  742   3
5  John  654   3

how could i do that? i only manage to get lot of guys with different counts.

thanks

Answer 1

Use an aggregate Query:

select g.ID, g.Name, g.Phone, count(*) over ( partition by g.name ) as Count
from 
Guys g;