Query to select duplicate fields
 

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
but I would like it to give me this:
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!

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?

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.

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]

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

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.

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]