count without group

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

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.

Use an aggregate Query:

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

You can still use a GROUP BY for the count, you just need to JOIN it back to your original table to get all the records, like this:

select g.ID, g.Name, g.Phone, gc.Count
from Guys g
inner join (
    select Name, count(*) as Count
    from Guys
    group by Name
) gc on g.Name = gc.Name

In Oracle DB you can use

SELECT ID,NAME,PHONE,(Select COUNT(ID)From GUYS GROUP BY Name) FROM GUYS ;

select id, name, phone,(select count(name) from users u1 where u1.name=u2.name) count from users u2

DECLARE @tbl table 
(ID int,NAME varchar(20), PHONE int)
insert into @tbl
select 
1  ,'John',  335   
union all
select 
2  ,'Harry', 444
union all
select 
3  ,'James', 367
union all
select 
4  ,'John',  742
union all
select 
5  ,'John',  654

SELECT
 ID
 , Name
 , Phone
 , count(*) over(partition by Name)
FROM @tbl 
ORDER BY ID

try

select column1, count(1) over ()

it should help