Sum amounts for one to many. - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Sum amounts for one to many.

Author

Topic

danyeung
Posting Yak Master

102 Posts

Posted - 2006-09-19 : 11:04:57

The following SQL statement returned incorrect Allocation Amount. Please see the examples. Can you please revise the SQL statement?

select Distributor.Name, sum(AllocationFund.Amount) as Allocaiton,
sum(isNULL(Request.mnyAmount, 0)) RequestAmount,
from Distributor inner join AllocationFund
on Distributor.DistributorID = AllocationFund.DistributorID
left outer join Request
on AllocationFund.AllocTypeID = Request.AllocTypeID
and Distributor.DistributorID = Request.distributorID
group by Distributor.Name
order by Distributor.Name

Data for Distributor1
Allocation Fund 1 = 45000
Allocation Fund 2 = 3000
Allocation Fund 3 = 1875

Data in Request
Request Amount for Fund 1 = 684.76
Request Amount for Fund 1 = 29600
Request Amount for Fund 2 = 1984.85
Request Amount for Fund 2 = 11998.52
Request Amount for Fund 2 = 444.58
Request Amount for Fund 2 = 375.14

The Result should be:
Name = Distributor1
Allocation = 49875 (45000 + 3000 + 1875)
RequestAmount = 45088 (684.76 + 29600 + 1984.85 + 11998.52 + 444.58 + 375.14)

But I got the following result:
Result: Name = Distributor1
Allocation = 103875 (45000 * 2 + 3000 * 4 + 1875)
RequestAmount = 45088

Thanks.
DanYeung

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-19 : 11:13:12

try this


select	Distributor.Name, 
	sum(AllocationFund.Amount) as Allocaiton,
	sum(isNULL(sum_mnyAmount, 0)) as RequestAmount,
from 	Distributor inner join AllocationFund
	on 	Distributor.DistributorID = AllocationFund.DistributorID
	left outer join 
	(
		select	distributorID, AllocTypeID, sum(mnyAmount) as sum_mnyAmount
		from	Request
		group by distributorID, AllocTypeID
	) req
	on	AllocationFund.AllocTypeID = req.AllocTypeID
	and 	Distributor.DistributorID = req.distributorID
group by Distributor.Name
order by Distributor.Name

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 11:15:58

Try to drop the alloctype on the join for Request.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 11:18:56

[code]SELECT d.Name,
af.AllocTypeID,
ISNULL(af.Allocation, 0) Allocation,
ISNULL(r.RequestAmount, 0) RequestAmount
FROM Distributor d
LEFT JOIN (
SELECT DistributorID,
AllocTypeID,
SUM(Amount) Allocation
FROM AllocationFund
GROUP BY DistributorID,
AllocTypeID
) af ON af.DistributorID = d.DistributorID
LEFT JOIN (
SELECT DistributorID,
AllocTypeID,
SUM(mnyAmount) RequestAmount
FROM Request
GROUP BY DistributorID,
AllocTypeID
) r ON r.DistributorID = af.DistributorID AND r.AllocTypeID = af.AllocTypeID[/code]

Peter Larsson
Helsingborg, Sweden

danyeung
Posting Yak Master

102 Posts

Posted - 2006-09-19 : 11:49:38

quote:

Originally posted by khtan

try this


select	Distributor.Name, 
	sum(AllocationFund.Amount) as Allocaiton,
	sum(isNULL(sum_mnyAmount, 0)) as RequestAmount,
from 	Distributor inner join AllocationFund
	on 	Distributor.DistributorID = AllocationFund.DistributorID
	left outer join 
	(
		select	distributorID, AllocTypeID, sum(mnyAmount) as sum_mnyAmount
		from	Request
		group by distributorID, AllocTypeID
	) req
	on	AllocationFund.AllocTypeID = req.AllocTypeID
	and 	Distributor.DistributorID = req.distributorID
group by Distributor.Name
order by Distributor.Name

Thanks.
DanYeung

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-19 : 11:51:05

Peter Larsson
Helsingborg, Sweden

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-09-19 : 20:21:11

quote:
Originally posted by Peso

Peter Larsson
Helsingborg, Sweden

Yeah, I'm not hip on the lingo, could you please translate the above quote? Thanks.
--Nick

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-20 : 00:58:30

It was a message to khtan, why beat me with a few minutes with the same solution.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-20 : 01:03:05

It basically means shoot the yak

danyeung
Posting Yak Master

102 Posts

Posted - 2006-11-01 : 14:23:23

quote:

Originally posted by khtan

try this


select	Distributor.Name, 
	sum(AllocationFund.Amount) as Allocaiton,
	sum(isNULL(sum_mnyAmount, 0)) as RequestAmount,
from 	Distributor inner join AllocationFund
	on 	Distributor.DistributorID = AllocationFund.DistributorID
	left outer join 
	(
		select	distributorID, AllocTypeID, sum(mnyAmount) as sum_mnyAmount
		from	Request
		group by distributorID, AllocTypeID
	) req
	on	AllocationFund.AllocTypeID = req.AllocTypeID
	and 	Distributor.DistributorID = req.distributorID
group by Distributor.Name
order by Distributor.Name

The above query returns all records in AllocationFund table althought there is no spending against the allocation fund in Request table. How do I also get the records that are in Request table but not in AllocationFund table?

Thanks.
DanYeung

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources