KP Adrian


I have a database that tracks billing and payment history records against a "relationship" record (the "relationship" maps a many-to-many relationship between employees and cell phone numbers).

I have two statements that look like this:

SELECT CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed
FROM Relationship
INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone
INNER JOIN BillingHistory ON Relationship.PKRelationship = BillingHistory.FKRelationship
GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber

SELECT CellPhone.PhoneNumber, SUM(PaymentHistory.AmountPaid) AS TotalPaid
FROM Relationship
INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone
INNER JOIN PaymentHistoryON Relationship.PKRelationship = PaymentHistory.FKRelationship
GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber

Each statement correctly aggregates the sums, but I need a record that shows me:

CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed, SUM(PaymentHistory.AmountPaid) AS TotalPaid

I can't figure out how to join or merge the statements together to get all of this information into one record without ruining the sums (I can't seem to correctly join the PaymentHistory table to the BillingHistory table without the sums going haywire).

Any help is appreciated.



Re: Combine columns from Two SELECT Statements

hunchback


Use each query as a derived table.

select

coalesce(a.PhoneNumber, b.PhoneNumber) as PhoneNumber,

a.TotalOwed,

b.TotalPaid

from

(

query A

) as a

full join

(

query B

) as b

on a.PhoneNumber = b.PhoneNumber

AMB






Re: Combine columns from Two SELECT Statements

Steve Kass

You could try this. It might be less efficient, but you never know.

select

cellPhone.PhoneNumber,

(select sum(BillingHistory.AmountOwed)

from RelationShip

join BillingHistory

on Relationship.PKRelationship = BillingHistory.FKRelationship

where CellPhone.PKCellPhone= Relationship.FKCellPhone) as TotalOwed,

(select sum(PaymentHistory.AmountPaid)

from Relationship

join PaymentHistory

on Relationship.PKRelationship = PaymentHistory.FKRelationship

where CellPhone.PKCelPhone = Relatinship.FKCellPhone) as TotalPaid

from CellPhone

I'm not sure I see where GROUP BY Relationship.PKRelationship helps you here, but it could be needed somewhere.

Steve Kass

Drew University

www.stevekass.com






Re: Combine columns from Two SELECT Statements

davidbrit2

Why not just do something like this


Code Snippet

SELECT CellPhone.PhoneNumber, ISNULL(SUM(BillingHistory.AmountOwed), 0) AS TotalOwed, ISNULL(SUM(PaymentHistory.AmountPaid), 0) AS TotalPaid
FROM CellPhone
LEFT OUTER JOIN OwedRelationship
ON CellPhone.PKCellPhone = OwedRelationship.FKCellPhone
LEFT OUTER JOIN BillingHistory
ON OwedRelationship.PKRelationship = BillingHistory.FKRelationship
LEFT OUTER JOIN PaidRelationship
ON CellPhone.PKCellPhone = PaidRelationship.FKCellPhone
LEFT OUTER JOIN PaymentHistory
ON PaidRelationship.PKRelationship = PaymentHistory.FKRelationship
GROUP BY OwedRelationship.PKRelationship, PaidRelationship.PKRelationship, CellPhone.PhoneNumber







Re: Combine columns from Two SELECT Statements

Steve Kass

David,

If you join all the tables together this way, the "sums will go haywire," as noted in the original post. Each AmountOwed value will appear multiple times in the sum - once for each AmountPaid value for the same account - and vice versa, so the query will not produce the desired result.

SK




Re: Combine columns from Two SELECT Statements

KernProbation

Steve Kass wrote:

You could try this. It might be less efficient, but you never know.

select

cellPhone.PhoneNumber,

(select sum(BillingHistory.AmountOwed)

from RelationShip

join BillingHistory

on Relationship.PKRelationship = BillingHistory.FKRelationship

where CellPhone.PKCellPhone= Relationship.FKCellPhone) as TotalOwed,

(select sum(PaymentHistory.AmountPaid)

from Relationship

join PaymentHistory

on Relationship.PKRelationship = PaymentHistory.FKRelationship

where CellPhone.PKCelPhone = Relatinship.FKCellPhone) as TotalPaid

from CellPhone

I'm not sure I see where GROUP BY Relationship.PKRelationship helps you here, but it could be needed somewhere.

Steve Kass

Drew University

www.stevekass.com



This (correctly) sums up the totals by phone number, but I need them summed up by Relationship (a relationship between an employee and a phone number), to distinguish the different owners of a single cell phone number.




Re: Combine columns from Two SELECT Statements

KernProbation

Steve Kass wrote:
David,

If you join all the tables together this way, the "sums will go haywire," as noted in the original post. Each AmountOwed value will appear multiple times in the sum - once for each AmountPaid value for the same account - and vice versa, so the query will not produce the desired result.

SK

This is exactly what does happen when I try David's solution.




Re: Combine columns from Two SELECT Statements

KernProbation

hunchback wrote:

Use each query as a derived table.

select

coalesce(a.PhoneNumber, b.PhoneNumber) as PhoneNumber,

a.TotalOwed,

b.TotalPaid

from

(

query A

) as a

full join

(

query B

) as b

on a.PhoneNumber = b.PhoneNumber

AMB


This seems almost correct, because the result set contains all of the rows I need, but it contains a lot of extra ones too. with erroneous data.

For example, I may get set that looks like:

Phone1 Owed1 Paid1
Phone2 Owed2 Paid1
Phone2 Owed2 Paid2
Phone3 Owed2 Paid3
Phone3 Owed3 Paid3

etc... with the bold rows being correct. The "correct" rows are all over the result set so I can't just cut out every other row.







Re: Combine columns from Two SELECT Statements

Steve Kass

You should be able to adapt it to sum by whatever you want. For example, if you want it summed by PhoneNumber and Relationship, proceed as follows.

1. Write a query that produces all the groups you want data for

select -- no sums of money data yet
cellPhone.PhoneNumber,
Relationship.PKRelationship
from <whatever is needed>

Then add the sums - figure out just how to get the sum for a specific PhoneNumber and Relationship and that will basicaly be your subquery. You will need to match both phone number and relationship with the outer tables, not just phone number. The results should look like this in outline:

select
C.PhoneNumber,
R.PKRelationship,
(
select sum(AmountOwed)
from ...
where CellPhone.PhoneNumber = C.PhoneNumber
and Relationship.PKRelationship = R.PKRelationship
)
from Relationship as R
join CellPhone as C
on ...

SK




Re: Combine columns from Two SELECT Statements

KernProbation

Steve Kass wrote:
You should be able to adapt it to sum by whatever you want. For example, if you want it summed by PhoneNumber and Relationship, proceed as follows.

1. Write a query that produces all the groups you want data for

select -- no sums of money data yet
cellPhone.PhoneNumber,
Relationship.PKRelationship
from <whatever is needed>

Then add the sums - figure out just how to get the sum for a specific PhoneNumber and Relationship and that will basicaly be your subquery. You will need to match both phone number and relationship with the outer tables, not just phone number. The results should look like this in outline:

select
C.PhoneNumber,
R.PKRelationship,
(
select sum(AmountOwed)
from ...
where CellPhone.PhoneNumber = C.PhoneNumber
and Relationship.PKRelationship = R.PKRelationship
)
from Relationship as R
join CellPhone as C
on ...

SK

Steve, you and I are now best friends. As soon as I dropped the INNER JOINs from the sub queries and used WHERE clauses, your solution worked.

Thanks a million!




Re: Combine columns from Two SELECT Statements

hunchback

Comming from Steve Kass, no doubt it will work. Did you try using the queries as derived tables

Code Snippet

select

coalesce(a.PhoneNumber, b.PhoneNumber) as PhoneNumber,

coalesce(a.PKRelationship, b.PKRelationship) as PKRelationship,

a.TotalOwed,

b.TotalPaid

from

(

SELECT

Relationship.PKRelationship,

CellPhone.PhoneNumber,

SUM(BillingHistory.AmountOwed) AS TotalOwed
FROM

Relationship
INNER JOIN

CellPhone

ON CellPhone.PKCellPhone = Relationship.FKCellPhone
INNER JOIN

BillingHistory

ON Relationship.PKRelationship = BillingHistory.FKRelationship
GROUP BY

Relationship.PKRelationship, CellPhone.PhoneNumber

) as a


full outer join

(
SELECT

Relationship.PKRelationship,

CellPhone.PhoneNumber,

SUM(PaymentHistory.AmountPaid) AS TotalPaid
FROM

Relationship
INNER JOIN

CellPhone

ON CellPhone.PKCellPhone = Relationship.FKCellPhone
INNER JOIN

PaymentHistory

ON Relationship.PKRelationship = PaymentHistory.FKRelationship
GROUP BY

Relationship.PKRelationship, CellPhone.PhoneNumber
) as b

on a.PKRelationship = b.PKRelationship

and a.PhoneNumber = b.PhoneNumber

AMB





Re: Combine columns from Two SELECT Statements

KernProbation

Tested and this solution works, too!