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; 

Answer 2

Update for 8.0+: This answer was written well before MySQL version 8, which introduced window functions with mostly the same syntax as the existing ones in Oracle.

In this new syntax, the solution would be

SELECT   t.name,   t.phone,   COUNT('x') OVER (PARTITION BY t.name) AS namecounter FROM   Guys t 

The answer below still works on newer versions as well, and in this particular case is just as simple, but depending on the circumstances, these window functions are way easier to use.

---

Older versions: Since MySQL, until version 8, didn't have analytical functions like Oracle, you'd have to resort to a sub-query.

Don't use GROUP BY, use a sub-select to count the number of guys with the same name:

SELECT   t.name,   t.phone,   (SELECT COUNT('x') FROM Guys ct     WHERE ct.name = t.name) as namecounter FROM   Guys t 

You'd think that running a sub-select for every row would be slow, but if you've got proper indexes, MySQL will optimize this query and you'll see that it runs just fine.

In this example, you should have an index on Guys.name. If you have multiple columns in the where clause of the subquery, the query would probably benefit from a single combined index on all of those columns.