busboy — 2010-12-28T15:02:33-05:00 — #1
My database has tons of duplicate accounts in the users table. Is there a good query that would show me potential duplicates?
system — 2010-12-28T15:17:47-05:00 — #2
SELECT acc_num, COUNT(acc_num)
GROUP BY acc_num
HAVING COUNT(acc_num) > 1
busboy — 2010-12-28T16:08:33-05:00 — #3
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?
SELECT concat(firstname,' ', lastname) AS Person, count(Person) as Total
group by Person
order by Total;
system — 2010-12-28T16:59:06-05:00 — #4
What output did you get when you ran the query?
busboy — 2010-12-28T17:08:02-05:00 — #5
1054 - Unknown column 'Person' in 'field list'
system — 2010-12-28T17:15:25-05:00 — #6
The error is telling you exactly what the problem is, so the fix is quick and simple.
busboy — 2010-12-28T17:24:57-05:00 — #7
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.
system — 2010-12-28T17:53:16-05:00 — #8
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.
busboy — 2010-12-28T18:02:54-05:00 — #9
Thank you Sir. May the Lord bless you for your willingness to help others.
system — 2010-12-28T18:05:41-05:00 — #10
busboy — 2010-12-29T23:30:18-05:00 — #11
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.
SELECT concat(firstname,' ', lastname) AS person, count(concat(firstname,' ', lastname)) AS total
group by person
HAVING COUNT(concat(firstname,' ', lastname)) > 1
order by total limit 5
r937 — 2010-12-29T23:45:31-05:00 — #12
SELECT COUNT(*) AS how_many_dupes
FROM ( SELECT firstname
HAVING COUNT(*) > 1 ) AS d
busboy — 2010-12-30T00:27:45-05:00 — #13
r937 you come through again! How can I send you some compensation?
r937 — 2010-12-30T06:33:19-05:00 — #14
help someone else
pay it forward