September 25, 2010 at 8:27 pm
Hello,
The table design is as follows
CREATE TABLE [dbo].[test](
[name] [nvarchar](50) NULL,
[number] [nvarchar](40) NULL,
[amount] [decimal](20, 2) NULL
) ON [PRIMARY]
GO
insert into test values('john','2A','342')
insert into test values('robert','2A','342')
insert into test values('cindy','2A','342')
insert into test values('missy','2A','342')
insert into test values ('jackie','3C','206')
insert into test values('donna','3c','206')
insert into test values('jamie','3d','205')
insert into test values('jay','3d','124')
insert into test values ('eric','2A','342')
The Query I used
select name=max(name),amount,number,count(*) as duplicates
from test
group by number,amount
having COUNT(number)>1 and MIN(name)<>MAX(name)
order by COUNT(*) desc
Query output:
name amount number duplicates
robert342.002A5
jackie206.003C2
Please let me know how can I list all the different names who have same number and amount and the number of duplicates and not just the maximum name.
For example
I want john,cindy,missy,eric also to appear instead of only robert that has the same number and amount.
Thank you!!
September 25, 2010 at 8:46 pm
Does this give you the results you were looking for? (You weren't really specific about the results you wanted to get.)
SELECT
Dupes.amount,
Dupes.number,
Dupes.duplicates,
DupePeople.name
FROM (SELECT
amount,
number,
COUNT(*) as duplicates
FROM dbo.test
GROUP BY
number,
amount
HAVING
COUNT(*) > 1) Dupes
INNER JOIN dbo.test DupePeople
ON Dupes.amount = DupePeople.amount
AND Dupes.number = DupePeople.number
ORDER BY
Dupes.duplicates DESC,
Dupes.amount,
Dupes.number,
DupePeople.name;
BTW: Thanks for posting the code to setup the table and insert the sample data.
September 25, 2010 at 9:28 pm
Thank you so much for the reply!! that did work but Could you please let me know a way to display the output in the following way:
I am not sure if this can be done through SQl
name name name name name number amount duplicates
john robert cindy missy eric 2A 342 5
jackie donna null null null 3c 206 2
September 25, 2010 at 9:52 pm
It can be done, but it can get complicated depending on how many duplicates it needs to support. Would there ever be more than 5 duplicates for a set of number and amount values?
What about having one column with a comma separated list of names that share the same number and amount?
September 25, 2010 at 10:14 pm
The number of duplicates can go to a number sometimes greater than 50.
Could you please let me know how to build a comma separated list of names that share the same number and amount. That would work wonderfully. I was not aware we could do that.
September 26, 2010 at 12:07 am
You can do almost anything, and there are usually multiple ways to solve a given problem.
Here is some code that I think will do what you want:
SELECT
Dupes.amount,
Dupes.number,
COUNT(*) as duplicates,
STUFF((SELECT
', ' + CAST(DupePeople.name AS VARCHAR(MAX))
FROM dbo.test DupePeople
WHERE
DupePeople.amount = Dupes.amount
AND DupePeople.number = Dupes.number
ORDER BY
DupePeople.name
FOR XML PATH(''), TYPE).value('.[1]','varchar(max)')
,1,2,'') AS Names --Use STUFF to remove the initial ", " from the returned list
FROM dbo.test Dupes
GROUP BY
Dupes.number,
Dupes.amount
HAVING
COUNT(*) > 1
ORDER BY
duplicates DESC,
Dupes.amount,
Dupes.number;
There are some limitations, like the name column can't contain special characters.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply