?
Solved

Query to group and count records

Posted on 2011-10-12
3
Medium Priority
?
261 Views
Last Modified: 2013-11-05
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
0
Comment
Question by:Phil Chapman
3 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 2000 total points
ID: 36957291
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
 
LVL 25

Expert Comment

by:TempDBA
ID: 36957806
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
 
LVL 8

Expert Comment

by:Crashman
ID: 36957990
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

839 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