Solved

SQL Syntax to Find and Count Duplicates of concatenated fields where Count > 1

Posted on 2007-11-26
8
1,439 Views
Last Modified: 2012-08-14
I have a table Tbl1 with the following fields:
ID (INT)
A  (varchar)
B  (INT)
C  (varchar)
D  (varchar)

Typical values are:
ID          A          B            C              D
1          aaa       1           xxxx          yyyy
2          bbb       1           dddd         eeee
3          aaa       1           xxxx          yyyy
4          ccc         2          hhhh         gggg
5          aaa       2           hhhh         gggg

I need SQL Syntax to Select the count of entries, grouped by A, where the concatenation of B & C & D is identical and where the count of such entries is greater than 1.

The result of the query in this case would be a single row:

A             Count_Of_Duplicates
aaa                   2
0
Comment
Question by:wsturdev
  • 3
  • 3
  • 2
8 Comments
 
LVL 27

Accepted Solution

by:
MikeToole earned 250 total points
ID: 20351443
I think that this should do it

Select A, Count(A)
From ...
group by B + C + D
Having Count( B + C + D) > 1
0
 
LVL 1

Author Comment

by:wsturdev
ID: 20352313
Don't mean to be so dense, but what is
<From ...>?
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 20352356
<From ...>
From YourTable Where somefield = something

Basically ... indicates that something was left out, i.e your table name and any Where clause you may need.

I've also assumed that your SQL database engine uses a '+' to join strings together.  
0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 250 total points
ID: 20352601

SELECT A, COUNT(*) AS Count_Of_Duplicates

FROM Tbl1

GROUP BY A, B+C+D

HAVING COUNT(*) > 1

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 25

Expert Comment

by:imitchie
ID: 20352604
yes which database are you using please?
0
 
LVL 1

Author Comment

by:wsturdev
ID: 20352653
SQL Server
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20352663
for MS Access, use this
SELECT A, COUNT(*) AS Count_Of_Duplicates

FROM Table2

GROUP BY A, B & C & D

HAVING COUNT(*) > 1

Open in new window

0
 
LVL 1

Author Comment

by:wsturdev
ID: 20352851
MIkeToole and imitchie, I am going to split the points between you.  YOur combined responses got me to investigating further.

This is what finally worked:
SELECT A, COUNT(*) AS Duplicate_Count FROM Tbl1 tbl WHERE EXISTS
(SELECT Count(ID), (CAST(B AS varchar(20)) + C + D) AS ComparisonVal FROM Tbl1
WHERE (CAST(B AS varchar(20)) + C + D) = (CAST(tbl.B AS varchar(20)) + tbl.C + tbl.D)
AND A = tbl.A  
GROUP BY (CAST(B AS varchar(20))+C+D)
HAVING COUNT(ID)>1)
GROUP BY A
ORDER BY A
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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 …
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

896 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

12 Experts available now in Live!

Get 1:1 Help Now