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  mo.*,
        (
        SELECT  COUNT(*)
        FROM    mytable mi
        WHERE   mi.name = mo.name
        )
FROM    mytable mo

Answer 2

SELECT `name`,
    (
        SELECT COUNT(*)
        FROM `table` AS `alt`
        WHERE `alt`.`name` = `table`.`name`
    ) AS `num`
FROM `table`

Answer 3

If you don't want to use a subquery, you can also join the table to itself like so: That would require a subquery. Perhaps something like this:

SELECT t1.name, COUNT(t2.name)
FROM my_table AS t1
INNER JOIN my_table AS t2 ON (t1.primary_key_field = t2.primary_key_field)
WHERE some_conditions
GROUP BY t1.name

Answer 4

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

Answer 5

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