Have Geek Brain

SQL Query to find duplicates that return no results

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

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

COUNT ([ACCT NBR 1]) AS NumOccurences

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

GROUP [CARD NUMBER], [CLIENT NAME], [ACCT NBR 1], [ACCT NBR 2]

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

+2


a source to share


4 answers


I think there is no error in your request, but it might 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]
+1


a source


You mean that the number as well as possible, with the help of "duplicate account numbers" ACCT NBR 1

and ACCT NBR 2

(either the same or different records)? Your query will not catch this situation.



+2


a source


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

0


a source


The problem is your data is not normalized. You can use the below query to find duplicate account numbers, you can run the results or nest them in a subquery to join the original data to grab customer names and card numbers. Another problem with your request is that you include the card number and name in the group, which means if two different people or cards have an account number, you will not find it.

Edit: This is actually a fairly common pattern that I found on re-searching. You need to detect duplicates by grouping them only in the column you want to find duplicates in, and then you need 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 return all other columns as a 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
0


a source






More articles:


All Articles