A query to find duplicate accounts

My database has tons of duplicate accounts in the users table. Is there a good query that would show me potential duplicates?

Thanks!

SELECT acc_num, COUNT(acc_num)
FROM accounts
GROUP BY acc_num
HAVING COUNT(acc_num) > 1

Thank you for your post. I used it to work on a more refined one for what I need. I need to be able to determine in an exact firstname lastname combination exists in the users table. Am I getting close on this one?

Thanks!

SELECT concat(firstname,’ ', lastname) AS Person, count(Person) as Total
from users
group by Person
order by Total;

What output did you get when you ran the query?

#1054 - Unknown column ‘Person’ in ‘field list’

The error is telling you exactly what the problem is, so the fix is quick and simple.

Yes, there is no “Person” column because I’m using “AS” to produce this new column on the fly. The fix is not obvious to me, that is why I came here seeking help.

Thank you.

ok then :slight_smile:

This is where developing your skill in thinking logically can help speed up debugging code.

Since the column Person doesn’t exist, try substituting Person for what it really is.

SELECT concat(firstname,' ', lastname) AS Person, count(concat(firstname,' ', lastname)) as Total

and leave the rest of the query as is.

Thank you Sir. May the Lord bless you for your willingness to help others.

you’re welcome :slight_smile:

Ok, I’m back again. This has become such a complex query, how do I go about just getting a simple count of how many rows this query produces? When I run the query right now I get 831 rows. I need to tweak this one or make a new one that simply tells me 831.

Thanks!


SELECT concat(firstname,' ', lastname) AS person, count(concat(firstname,' ', lastname)) AS total
from users
group by person
HAVING COUNT(concat(firstname,' ', lastname)) > 1
order by total limit 5

SELECT COUNT(*) AS how_many_dupes
  FROM ( SELECT firstname
              , lastname
           FROM users
         GROUP 
             BY firstname
              , lastname
         HAVING COUNT(*) > 1 ) AS d

:slight_smile:

r937 you come through again! How can I send you some compensation?

help someone else

pay it forward

:cool: