Post reply

Query reports duplicates when running a GROUP BY but not when I try to see the actual records?

  • November 7, 2013 at 6:51 am

    #300028

    I have a query that joins several tables and I am trying to identify duplicates, so I am running some COUNT/GROUP BY/HAVING queries on various fields like User ID, Last Name + First Name + Middle Name and SSN. When I run it on SSN I get two

    SELECT Ssn, COUNT(*)

    FROM tables and joins

    WHERE clauses, ...

    GROUP BY Ssn

    HAVING COUNT(*) > 1

    ORDER BY Ssn

    Produces:

    Ssn Count

    1234567890 2

    0123456789 2

    But when I try to see the records in question using the exact same FROM/JOINS/WHERE clause, they don't appear to actually be present in the data:

    SELECT *

    FROM tables and joins

    WHERE clauses, ...

    AND Ssn IN ('1234567890', '0123456789')

    Produces no records.

    Is this possibly a bad index???

    Thanks in advance!!!

  • November 7, 2013 at 7:40 am

    #1664767

    You'll probably have to post actual sample tables/data/queries for anyone to help you with this, but try something like this:

    SELECT *

    FROM tables and joins

    WHERE clauses, ...

    AND Ssn IN

    (

    SELECT Ssn

    FROM tables and joins

    WHERE clauses, ...

    GROUP BY Ssn

    HAVING COUNT(*) > 1

    )

  • November 7, 2013 at 9:51 am

    #1664847

    Thanks, your reply was very helpful and led me to the issue. By removing each join and it's criteria one at a time I was able to identify the joined tables that had more than one record in them.

    The issue as I described it was actually caused by human error when I wrote the SSN numbers down (incorrectly, apparently, causing them to not show up).

    Doh!

    Thanks again!!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply