Solved

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

Posted on 2007-11-19
3
234 Views
Last Modified: 2012-05-05
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
Comment
Question by:ccleebelt
3 Comments
 
LVL 16

Accepted Solution

by:
SQL_SERVER_DBA earned 500 total points
ID: 20314287
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
 

Author Closing Comment

by:ccleebelt
ID: 31409990
Thanks
0
 
LVL 6

Expert Comment

by:wshark83
ID: 20314364
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Create a linked  server to connect ot an Access Database. 26 46
Stored Procedure error 45 47
How to enforce inte 8 43
insert query with value having 's 2 47
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now