Duplicate Data shown in the Query output

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

Duplicate Data shown in the Query output

Author

Topic

Tina1225
Starting Member

1 Post

Posted - 2010-10-06 : 11:08:34

Hello,

CREATE TABLE [dbo].[test2](
[name] [varchar](20) NULL,
[id] [varchar](20) NOT NULL,
[country] [varchar](20) NULL,
[state] [varchar](20) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[costdetail](
[name] [varchar](20) NULL,
[cost] [decimal](20, 0) NULL
) ON [PRIMARY]

GO

I need the name,country, state, totalcost and record count(in cost detail table) for each vendor name and I am trying the following query

select drv.name,id,state,country,drv.spend,drv.recordcnt
from
(select test2.name,SUM(cost) as spend,COUNT(*) as recordcnt
from test2 join costdetail on test2.name=costdetail.name
where country<>'usa'
group by test2.name) as drv join
(select name,id,state,country from test2 where country<>'usa'
) as dvr
on drv.name=dvr.name

But i getting duplicated results in the Query output. Some of the records are being duplicated 3 to 4 times in the output. I am getting something as follows.

Walmart,alberta,canada,320, 3
jcpenney,alberta,canada,564,1
Walmart,alberta,canada,320, 3
Walmart,alberta,canada,320, 3
walgreens,sydney,australia, 6730 12
walgreens,sydney,australia, 6730 12
cubs,sydney,australia,4230,1
cubs,sydney,australia,4230,1

Please help me with the above problem.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-06 : 11:22:55

Try this:

Select test2.name,state,country,SUM(cost) as spend,COUNT(*) as recordcnt
from test2 join costdetail on test2.name=costdetail.name
where country<>'usa'
Group by test2.name,state,country

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-06 : 11:23:45

I think this will do it:
select t2.name,t2.id,t2.country,t2.state,dt.spend,dt.recordcnt
from test2 as t2
join
(select name,sum(cost) as spend,count(*) as recordcnt
from costdetail group by name)dt
on dt.name=t2.name

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources