Query to group and count records

I need to identify duplicate records and the query below does that but I need to also include the records ID which is the field Customer

If I make the Customer part of the SELECT clause then I have to include it in the GROUP BY.  If this is done of course there are no duplicates.

Any help would be appreciated.

SELECT DISTINCT NameLast,NameFirst,Address_1,Address_2,City,State,COUNT(NameLast) AS TotalRec
FROM
Customers
WHERE Customer > 0
Group by  NameLast,NameFirst,Address_1,Address_2,City,State
HAVING COUNT(NameLast) > 2
ORDER BY
NameLast,NameFirst,Address_1,Address_2,City,State
LVL 2
Phil ChapmanAsked:
Who is Participating?
 
JestersGrindCommented:
You could make your current query a subquery or CTE and then join it back to the original table to get the extra column like this.

Greg


;WITH Duplicates 
AS
(
SELECT DISTINCT NameLast,NameFirst,Address_1,Address_2,City,State,COUNT(NameLast) AS TotalRec
FROM
Customers
WHERE Customer > 0
Group by  NameLast,NameFirst,Address_1,Address_2,City,State
HAVING COUNT(NameLast) > 2
ORDER BY
NameLast,NameFirst,Address_1,Address_2,City,State
)
SELECT a.CustomerID, b.*
FROM Customers a INNER JOIN Duplicates b ON a.NameLast = b.NameLast AND a.NameFirst = b.NameFirst AND a.Address_1 + b.Address_1
AND a.Address_2 = b.Address_2 AND a.City = b.City AND a.State = b.State

Open in new window

0
 
TempDBACommented:
you can achieve it from rank function or row number function. Below is a similar case:-

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_27393445.html#a36957787
0
 
CrashmanCommented:
Select * from (
SELECT DISTINCT NameLast,NameFirst,Address_1,Address_2,City,State,COUNT(NameLast) Over (Partition by NameLast) AS TotalRec
FROM
Customers
WHERE Customer > 0
ORDER BY
NameLast,NameFirst,Address_1,Address_2,City,State) as Der
where Der.TotalRec > 2
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.

All Courses

From novice to tech pro — start learning today.