Solved

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

Posted on 2007-11-26
8
1,441 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
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

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
 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to base a filter depending on fields contents? 15 59
SQL Server 2012 r2 - Make Temp Table Query Faster 5 44
sql server insert 12 30
TSQL query to generate xml 4 35
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

832 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