|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
|
#1
|
|||
|
|||
|
Sum for one to many in SQL.
Here is the SQL statement:
select Distributor.Name, sum(AllocationFund.Amount) as Allocaiton, round(sum(AllocationFund.Amount), 0) as RequestAmount, round(sum(AllocationFund.Amount) - sum(Request.Amount), 0) as Balance 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 (Result) Allocation Fund 1 = 45000 Allocation Fund 2 = 3000 Allocation Fund 3 = 1875 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) Balance = 4787 (49875 - 45088) But I got the following result: Result: Name = Distributor1 Allocation = 103875 (45000 * 2 + 3000 * 4 + 1875) RequestAmount = 45090.0000 Balance = 58790.0000 How can I get the correct result? Thanks. DanYeung |
![]() |
| Viewing: Dev Articles Community Forums > Databases > Microsoft SQL Server > Sum for one to many, change NULL to zero in SQL. |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|