MySQL: Count entries without grouping?

Question

I want to pull results and count how many of each name is pulled but without grouping...

for example I want this:

John Doe 3
John Doe 3
John Doe 3
Mary Jane 2
Mary Jane 2

instead of this:

John Doe 3
Mary Jane 2

Does that make sense?

Thanks.

Answer 1

SELECT b.name, a.the_count
FROM
  some_table b, 
  (SELECT name, COUNT(*) AS the_count
  FROM some_table
  GROUP BY name) AS a
WHERE b.name = a.name

Answer 2

This uses group by but gets the output in the format you want.

SELECT Name, NG.NameCount
FROM Names
INNER JOIN
    (SELECT Name, Count(1) As NameCount
    FROM Names
    GROUP BY Name) NG
ON Names.Name = NG.Name