Sum of One and not the Other
I have a requirement to query one figure and the sum of another.
What I need is one query to produce the MonthPayment for each CompanyID for a given ContractID and the Sum of TotalPayment with matching CompanyID and ParentCompanyID for each CompanyID
TableA
ContractID CompanyID MonthPayment TotalPayment
123 456 100 1000
123 789 200 3000
345 789 300 3000
321 456 400 1000
678 456 500 4000
678 789 600 5000
TableB
ParentCompanyID ContractID
DEF 123
DEF 345
ABC 678
DEF 321
This is what I came up with:
@ContractID = 123
Select
a.CompanyID,
Sum(a.MonthlyPayment) as MonthlyPayment,
Sum(a.TotalPayment) as TotalPayment
From TableA a Inner Join TableB b
On a.ContractID = b.ContractID
Where ParentCompanyID = (Select ParentCompanyID from TableB where TableB.ContractID = @ContractID)
Group by a.CompanyID
Which gets me this:
CompanyID MonthPayment TotalPayment
456 500 2000
789 500 6000
This is what I need:
CompanyID MonthPayment TotalPayment
456 100 2000
789 200 6000
I can write a query that will get me the MonthPayment I need, and a query to write the TotalPayment I need.
But I am trying to figure out how to write one query to do both.
If I don't Sum(MonthPayment), I get an error that says I need to include it in the Group By. If I add MonthPayment to the Group By, the SUM(TotalPayment) doesn't total.
Any suggestions will help.