Pat Egen’s Weblog

Ramblings, chats, thoughts, ideas

Posts Tagged ‘SQL Queries

SQL Tip of the Week – Finding Duplicates

leave a comment »

Many of you know we are the duplicate contact wizards for ACT.  But occasionally, we also have to find duplicates in other databases.  I have an Excel macro that either flags rows that are duplicates with a series of dashes and another one that color codes the duplicate rows.  I’m going to save those macros for another blog article.

Today I am going to show two tips I have in my bag of tricks (found somewhere long ago and I wish I could remember where so I could give credit.  The first statement finds duplicates in SQL tables and the second one will actually find items that appear only once.  Same technique with different results.

The first query will find date (ile. fields) that are duplicated in a table. This could be handy for finding duplicate emails, contact names, account numbers, you name it.

The query looks like this (in this case, I’m checking for duplicate contact names – called FullName)

SELECT FullName, 
 COUNT(FullName) AS NumOccurrences
FROM Contacts
GROUP BY FullName
HAVING (COUNT(Fullname) > 1)

In the statement above I am looking for the field “Fullname” from the table “Contacts”.  I look for Fullname that appears more than once (that’s the >1 piece).  At this point you could print out the results or save them to Excel.  You get the idea.

Taking it one step further, maybe you want to find things that appear only once and you are expecting them to be there more than once – i.e orders.  For example, you want to see in your database any one who has only had one order placed.  Assume in this case that there is a PO field (purchase order) and if  there is only one row, then there is only one order.

This is our query

SELECT PO
FROM Orders
GROUP BY PO
HAVING (COUNT(PO) = 1)

In the query above, we are looking for a field called PO in a table called Orders where PO = 1 or in other words, occurs only once.  Pretty simple.

I remember finding these years ago and they really helped with a project I was working on.  I’ve been spending time going back through my SQL and Access files to pull out these gems so I can share them just as some long forgotten soul did for me.

Thanks, whoever you are.


		
Advertisements

Written by pregen

July 14, 2010 at 6:55 pm

Posted in Uncategorized

Tagged with

%d bloggers like this: