SitePoint Sponsor

User Tag List

Results 1 to 14 of 14
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT acc_num, COUNT(acc_num)
    FROM accounts
    GROUP BY acc_num
    HAVING COUNT(acc_num) > 1

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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;
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by busboy View Post
    Am I getting close on this one?

    Thanks!
    What output did you get when you ran the query?

  5. #5
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    #1054 - Unknown column 'Person' in 'field list'
    Convert your dollars into silver coins. www.convert2silver.com

  6. #6
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The error is telling you exactly what the problem is, so the fix is quick and simple.

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Convert your dollars into silver coins. www.convert2silver.com

  8. #8
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok then

    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.

    Code SQL:
    SELECT concat(firstname,' ', lastname) AS Person, COUNT(concat(firstname,' ', lastname)) AS Total

    and leave the rest of the query as is.

  9. #9
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you Sir. May the Lord bless you for your willingness to help others.
    Convert your dollars into silver coins. www.convert2silver.com

  10. #10
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you're welcome

  11. #11
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!


    Code MySQL:
    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
    Convert your dollars into silver coins. www.convert2silver.com

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT COUNT(*) AS how_many_dupes
      FROM ( SELECT firstname
                  , lastname
               FROM users
             GROUP 
                 BY firstname
                  , lastname
             HAVING COUNT(*) > 1 ) AS d
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    r937 you come through again! How can I send you some compensation?
    Convert your dollars into silver coins. www.convert2silver.com

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    How can I send you some compensation?
    help someone else

    pay it forward

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •