Query Doubt - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Doubt

Author

Topic

tammy2512
Starting Member

8 Posts

Posted - 2010-09-25 : 22:24:45

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
robert 342.00 2A 5
jackie 206.00 3C 2

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

tammy2512
Starting Member

8 Posts

Posted - 2010-09-25 : 23:30:12

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

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-09-26 : 01:55:51

Which sql server version you are using ?

bayazidahmed
Starting Member

1 Post

Posted - 2010-09-26 : 07:13:47

Create a custom aggregate function using CLR to concatenate strings and use it in the query below

SELECT
SchemaName.CustomAggregate(Name), Amount, Number, COUNT(*) AS Occurences
FROM
test
GROUP BY
Number, Amount
HAVING
COUNT(*) > 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources