Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • 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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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