October 6, 2010 at 9:15 am
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.
October 6, 2010 at 9:23 am
That query can definitely be cleaned up, but could you please provide some sample data for both tables so we can make sure it's returning as expected?
October 6, 2010 at 9:54 am
Thank you for your response
insert into test2 values('j&j','1','canada','alberta')
insert into test2 values('j&j','2','canada','alberta')
insert into test2 values('walmart','2','uk','london')
insert into test2 values('cubs','3','australia','sydney')
insert into test2 values('walgreens','4','usa','minnesota')
insert into test2 values('jcpenney','5','usa','missouri')
insert into costdetail values('j&j','320')
insert into costdetail values('j&j','420')
insert into costdetail values('j&j','320')
insert into costdetail values('walmart','240')
insert into costdetail values('walmart','670')
insert into costdetail values('walgreens','670')
insert into costdetail values('jcpenney','345')
October 6, 2010 at 10:03 am
Something like this? :
CREATE TABLE [#test2](
[name] [varchar](20) NULL,
[id] [varchar](20) NOT NULL,
[country] [varchar](20) NULL,
[state] [varchar](20) NULL
) ON [PRIMARY]
CREATE TABLE [#costdetail](
[name] [varchar](20) NULL,
[cost] [decimal](20, 0) NULL
) ON [PRIMARY]
insert into #test2 values('j&j','1','canada','alberta')
insert into #test2 values('j&j','2','canada','alberta')
insert into #test2 values('walmart','2','uk','london')
insert into #test2 values('cubs','3','australia','sydney')
insert into #test2 values('walgreens','4','usa','minnesota')
insert into #test2 values('jcpenney','5','usa','missouri')
insert into #costdetail values('j&j','320')
insert into #costdetail values('j&j','420')
insert into #costdetail values('j&j','320')
insert into #costdetail values('walmart','240')
insert into #costdetail values('walmart','670')
insert into #costdetail values('walgreens','670')
insert into #costdetail values('jcpenney','345')
;WITH Totals AS (
SELECT NAME, SUM(COST) as [Sum] FROM #costdetail
GROUP BY NAME
)
SELECT DISTINCT t2.Name, Country, State, t.Sum FROM #test2 t2
LEFT JOIN Totals t ON t2.name = t.name
DROP TABLE #test2
DROP TABLE #costdetail
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply