sql duplicates showing all data

Question

Given this data

id  Name        group           

1   Jhon        001

2   Paul        002

3   Mary        001

How can I get the duplicates values showing all the fields? The duplicate is only on group, id and name won't be duplicates.

Should end up looking like one of those (any would be valid):

:::::::::::::::::::::::::::::::::::::::::::::::
group  count values

001     2     1,3

:::::::::::::::::::::::::::::::::::::::::::::::
id  name        group
1   Jhon        001
3   Mary        001

I tried with

SELECT
    group, COUNT(*)
FROM
    people
GROUP BY
    group
HAVING 
    COUNT(*) > 1

But if I try to add id and name to the group by, it won´t find any duplicate.

Thanks in advance.

the second is just `SELECT * FROM people WHERE [group] = '001'` — Mark Schultheiss, Nov 28 '19 at 11:34
Possible duplicate of [How to concatenate many rows with same id in sql?](https://stackoverflow.com/questions/35172956/how-to-concatenate-many-rows-with-same-id-in-sql) — Mark Schultheiss, Nov 28 '19 at 11:42

DineshDB · Accepted Answer · 2019-11-28T11:40:16.797

1

Try this.

SELECT Id, Name, [Group] 
FROM people
WHERE [Group] IN(
    SELECT [Group]
    FROM people
    GROUP BY [Group]
    HAVING COUNT(*) > 1)

edited Nov 28 '19 at 11:40

answered Nov 28 '19 at 11:30

DineshDB

5,998
7
33
49

score 1 · Answer 2 · edited Nov 28 '19 at 11:34

1

I would do an inner query to find the groups with more than one member, and then use that inner query to bring back a list of the names.

For example:

SELECT Id, Name, group
FROM people
WHERE group in
   (SELECT group
    FROM people
    GROUP BY group
    HAVING count(*) > 1);

edited Nov 28 '19 at 11:34

Amira Bedhiafi

8,088
6
24
60

answered Nov 28 '19 at 11:33

Tim Wooldridge

193
1
3
11

how does this differ from prior answer other than the comment/text, perhaps just suggest that addition to prior answer? – Mark Schultheiss Nov 28 '19 at 11:35
It doesn't. I think we both answered at the same time. – Tim Wooldridge Nov 28 '19 at 11:37

score 1 · Answer 3 · answered Nov 28 '19 at 11:37

1

Avoid using Group because it is a reserved keyword in SQL :

 SELECT *
    FROM MyTable
    WHERE groups IN(
        SELECT groups
        FROM MyTable
        GROUP BY groups
        HAVING COUNT(*) > 1)

Check Execution here

answered Nov 28 '19 at 11:37

Amira Bedhiafi

8,088
6
24
60

score 1 · Answer 4 · answered Nov 28 '19 at 13:00

Just use exists:

select p.*
from people p
where exists (select 1
              from people p2
              where p2.group = p.group and
                    p2.id <> p.id
             );

This should be the most performant solution. With an index on people(group, id), it should have very good performance.

Note: All the advice to avoid using group as a column name is good advice. You should change the name.

sql duplicates showing all data

4 Answers4