SQL Query to find duplicates not returning any results

Question

I know there are duplicate account numbers in this table, but this query returns no results.

SELECT [CARD NUMBER],[CUSTOMER NAME],[ACCT NBR 1],[ACCT NBR 2],

COUNT([ACCT NBR 1]) AS NumOccurences

FROM DebitCardData.dbo.['ATM Checking Accts - Active$']

GROUP BY [CARD NUMBER],[CUSTOMER NAME],[ACCT NBR 1],[ACCT NBR 2]

HAVING (COUNT([ACCT NBR 1])>1)

Answer 1

The query looks correct as far as I can tell. Show us some rows of duplicate data, and I can suggest a query to find them.

Answer 2

Perhaps by "duplicate account numbers" you mean a number is in both ACCT NBR 1 and ACCT NBR 2 (either for the same or different records)? Your query would not catch that situation.

Answer 3

i think there is no error in your query but this out may work for you

SELECT [CARD NUMBER],[CUSTOMER NAME],[ACCT NBR 1],[ACCT NBR 2],

NumOccurences

FROM DebitCardData.dbo.['ATM Checking Accts - Active$'] as accMailTbl

inner join 

(SELECT [ACCT NBR 1],COUNT([ACCT NBR 1]) AS NumOccurences

FROM DebitCardData.dbo.['ATM Checking Accts - Active$']

GROUP BY [ACCT NBR 1]  HAVING (COUNT([ACCT NBR 1])>1)) accTbl

on accTbl.[ACCT NBR 1]=accMailTbl.[ACCT NBR 1]

Answer 4

The problem is your data is not normalized. You can use the below query to find duplicate account numbers, you can stage the results or nest this in a subquery to join back to the original data to grab Customer Names and Card Numbers. Another problem with your query is that you include the card number and name in the group by, which means if two different people or cards share an account number, then you will not detect it.

Edit: This is actually a pretty common pattern I've found when doing duplicate searches. You have to detect the duplicates by grouping only on the column you want to find duplicates of, and then you have to nest that in a subquery or join to go back and find out what other data was related.

Select AccountNumber From
  (Select [CARD NUMBER],[CUSTOMER NAME],[ACCT NBR 1] as AccountNumber 
      From DebitCardData.dbo.['ATM Checking Accts - Active$']
  UNION
  Select [CARD NUMBER],[CUSTOMER NAME],[ACCT NBR 2] as AccountNumber 
      From DebitCardData.dbo.['ATM Checking Accts - Active$']
  ) as NormalizedDebitCardData
GroupBy AccountNumber
Having Count(*)>1

To get back all the other columns in the result:

Select [CARD NUMBER],[CUSTOMER NAME],[ACCT NBR 1], [ACCT NBR 2]
From DebitCardData.dbo.['ATM Checking Accts - Active$']
Inner Join
    (Select AccountNumber From
      (Select [CARD NUMBER],[CUSTOMER NAME],[ACCT NBR 1] as AccountNumber 
          From DebitCardData.dbo.['ATM Checking Accts - Active$']
      UNION
      Select [CARD NUMBER],[CUSTOMER NAME],[ACCT NBR 2] as AccountNumber 
          From DebitCardData.dbo.['ATM Checking Accts - Active$']
      ) as NormalizedDebitCardData
    Group By AccountNumber
    Having Count(*)>1) as AccountNumberDuplicates
On AccountNumberDuplicates.AccountNumber = [ACCT NBR 1] or AccountNumberDuplicates.AccountNumber = [ACCT NBR 2]
Order By AccountNumberDuplicates.AccountNumber