Query to select duplicate fields

[TheMilkCarton]

Hi, I have this:
[sql]
SELECT email,username,
COUNT(email) AS NumOccurrences
FROM user
GROUP BY email
HAVING ( COUNT(email) > 1 )
[/sql]

And it works just fine, but I was wondering how to make it so that it actually LISTS each occurence?

For example, it gives me this

username_____email_________ numOccurrences
user1 -------- blah@blah.com -----3
otheruser1 ---- ***@***.com -----4

but I would like it to give me this:

username_____email_________ numOccurrences
user1 -------- blah@blah.com -----3
user2 -------- blah@blah.com -----3
user3 -------- blah@blah.com -----3
otheruser1 ---- ***@***.com -----4
otheruser2 ---- ***@***.com -----4
otheruser3 ---- ***@***.com -----4
otheruser4 ---- ***@***.com -----4

This of course becomes redundant, and the "numOccurrences" column would be kind of unnecessary, but I'm really stupid with MySQL and can't seem to figure this out.

Thanks!

[Antivirus]

you're trying to find out how many times any unique email address appears in the db and list the different users that have those same email addresses?

[TheMilkCarton]

Yes. This is with a very old install of vBulletin, btw. I think vB 3.x is set up to never allow duplicate emails.

There are 35 emails that have been used at least twice, some up to 10 times, so I'd want a comprehensive list of the hundred or so usernames that have duplicate emails. The way I'm doing it now only shows me ONE of the usernames attached to the email.

I guess I could type out all 35 emails in a query? haha.

[Farcaster]

I'm at work and I don't have access to a MySQL database at the moment, but I think this query will do it for you:

[SQL]SELECT
u.username, u.email, count(*) as NumOccurences

FROM
user u
INNER JOIN user u2 ON u.email = u2.email

GROUP BY
u.username, u.email

HAVING
count(*) > 1[/SQL]

[TheMilkCarton]

Well, something went screwy and I had to KILL the process. I just about had a panic attack.

[Farcaster]

I ran this this morning on my own forum and it worked fine. It does do a table scan, I noticed. If you have a ton of users, you might try adding an index on the email field if there isn't one there already.

[Eikinskjaldi]

You could use group_concat

select username, group_concat(email), count(*) from user group by username having count(*) > 1

[TheMilkCarton]

Originally Posted by Eikinskjaldi

You could use group_concat



select username, group_concat(email), count(*) from user group by username having count(*) > 1

Thanks so much. I had to switch it up a bit, but thanks for the right direction.

It ended up looking like:

[sql]SELECT email, group_concat( username ) , COUNT( email )
FROM user
GROUP BY email
HAVING (COUNT( email ) >1)[/sql]