Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

Finding records that occur more than once in a recordset/table

Need some help with a query.

I have a table called "contacts" which has many fields, in particular, I am interested in contact_email and contact_last_name

I want to create query that will return on records that have contact_email and contact_last_name appearing more than once in the table so I can identify duplicates.

For example,

contact_email          contact_last_name
--------------------------------------------------
john@doe.com        Doe
jane@doe.com        Doe
john@doe.com        Doe

would return

john@doe.com, Doe, 2 based on the logic above thus identifying that John Doe is like a duplicate record.

Please provide actual query.  Thanks.
0
ccleebelt
Asked:
ccleebelt
1 Solution
 
SQL_SERVER_DBACommented:
select contact_email, contact_last_name
from tablename
group by contact_email, contact_last_name
HAVING COUNT(contact_email)>1 OR COUNT(contact_last_name)>1
0
 
ccleebeltAuthor Commented:
Thanks
0
 
wshark83Commented:
Same query but giving you the number of occurances for Emails and Lastname.

select contact_email, contact_last_name, count(contact_email) as EmailCount, COUNT(contact_last_name) as LastNameCount
from tablename
group by contact_email, contact_last_name
HAVING COUNT(contact_email)>1 OR COUNT(contact_last_name)>1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now