MySQL SELECT FROM WHERE COUNT

Question

Hi im looking for an MySQL Select that returns only that rows with doubled entries in column xxx

example:

+------+------+------------+--------------------+
| id   | name | work_date  | daily_typing_pages |
+------+------+------------+--------------------+
|    1 | John | 2007-01-24 |                250 |
|    2 | Ram  | 2007-05-27 |                220 |
|    3 | Jack | 2007-05-06 |                170 |
|    4 | Jack | 2007-04-06 |                100 |
|    5 | Jill | 2007-04-06 |                220 |
|    6 | Zara | 2007-06-06 |                300 |
|    7 | Zara | 2007-02-06 |                350 |
+------+------+------------+--------------------+

Got This Table and i want to read out all entries thats name is listed more than once, my Query is not working cause it just shows entries with two times the name just once

SELECT id, name, COUNT(name) AS count
    FROM table_xy   
    having count(name) > 1;

what i want to have returned:

+------+------+------------+
| id   | name |   count    | 
+------+------+------------+
|    3 | Jack |     2      |
|    4 | Jack |     2      |
|    6 | Zara |     2      |
|    7 | Zara |     2      |
+------+------+------------+

Is there a way to get that?

Answer 1

try this...

SELECT  x.ID, x.Name, COUNT(y.ID) + 1 AS count

FROM table_xy AS x

INNER JOIN table_xy AS y

ON x.Name = y.Name

AND x.ID != y.ID GROUP BY x.ID, x.Name;

Answer 2

You could use a subquery for your group by:

SELECT  x.id, y.name, y.count
FROM    table_xy AS x
        INNER JOIN
        (   SELECT  Name, COUNT(*) AS count
            FROM     table_xy
            GROUP BY Name
            HAVING COUNT(*) > 1
        ) AS y
            ON y.Name = x.Name;

Alternatively you could use a self join with distinct if you don't need the count:

SELECT  DISTINCT x.ID, x.Name
FROM    table_xy AS x
        INNER JOIN table_xy AS y
            ON x.Name = y.Name
            AND x.ID != y.ID;

Or a self join with GROUP BY if you do need the count:

SELECT  x.ID, x.Name, COUNT(y.ID) + 1 AS count
FROM    table_xy AS x
        INNER JOIN table_xy AS y
            ON x.Name = y.Name
            AND x.ID != y.ID
GROUP BY x.ID, x.Name;

Examples on SQL Fiddle