0

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.

1
Contributor
1
Reply
2
Views
6 Years
Discussion Span
Last Post by gspeedtech
0

My bad, the solution can be found in the reply I received from DarkAng to my other thread "Challenging Query for a Newb".

I did an Inner Join of both MonthlyPayment and TotalPayment Queries to get the results I was looking for.

Edited by gspeedtech: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.