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 desired 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 managed to get a lot of guys with different counts.

Thanks

Answer 1

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.

Answer 2

Use an aggregate Query:

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

Answer 3

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

Answer 4

In Oracle DB you can use

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

Answer 5

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

Answer 6

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

Answer 7

try

select column1, count(1) over ()