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?

no thats not the result i want if i do group by name it will show me just one entry for each different name... — xQp, Jun 04 '14 at 14:40

score 2 · Accepted Answer · answered Jun 04 '14 at 14:44

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

answered Jun 04 '14 at 14:44

GarethD

68,045
10
83
123

1

Prefer `USING (Name)` over `ON y.Name = x.Name` - I'm not sure if it applies to derived tables, but it's a good practice to have as not only does it reduce ambiguity ("Error: column X is ambiguous" or similar) but it also tends to make better use of indexes. – Niet the Dark Absol Jun 04 '14 at 14:46
Also, since you're using `HAVING`, you can use the `count` alias rather than the `COUNT(*)` function - pretty sure MySQL optimises that away, but still, can't hurt. – Niet the Dark Absol Jun 04 '14 at 14:46
Yeah that works perfect thank you, and thx Niet the Dark Absol for that USING(Name) info, didnt know that either ;) – xQp Jun 04 '14 at 14:50
@NiettheDarkAbsol Do you have anything to substantiate the fact that `USING` makes `better use of indexes`? In [this case at least](http://sqlfiddle.com/#!2/3e197/2) the execution plans are identical. With regard to your other points, I think they are entirely subjective, and I personally dislike the inflexibility of the using clause, (i.e. you can only use the equals operator), this leads to a mix of `USING` and explicit predicates throughout a query, and I am a big fan of consistency. – GarethD Jun 04 '14 at 15:06
@GarethD I'm afraid I don't have anything to substantiate that, sorry! I've just noticed better performance simply by changing between using `USING` and not using it. However, `USING` explicitly tells MySQL that the columns are equal, so for example `SELECT a FROM x JOIN y USING (a)` works whereas `SELECT a FROM x JOIN y ON x.a=y.a` results in `a` being ambiguous in the field list (and `SELECT x.a` doesn't communicate that feeling of sameness when reading the query) – Niet the Dark Absol Jun 04 '14 at 15:08

score 0 · Answer 2 · answered Jun 06 '14 at 07:36

0

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;

answered Jun 06 '14 at 07:36

Sanjaya

156
1
9

MySQL SELECT FROM WHERE COUNT

2 Answers2