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.

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

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

davidbrit2

Why not just do something like this

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

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

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

KernProbation

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.

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

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.

KernProbation

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

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.

KernProbation

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.

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.

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

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

KernProbation

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!

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!

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

FROMRelationship

INNER JOINCellPhone

ON CellPhone.PKCellPhone = Relationship.FKCellPhone

INNER JOINBillingHistory

ON Relationship.PKRelationship = BillingHistory.FKRelationship

GROUP BYRelationship.PKRelationship, CellPhone.PhoneNumber

) as a

full outer join

(

SELECTRelationship.PKRelationship,

CellPhone.PhoneNumber,

SUM(PaymentHistory.AmountPaid) AS TotalPaid

FROMRelationship

INNER JOINCellPhone

ON CellPhone.PKCellPhone = Relationship.FKCellPhone

INNER JOINPaymentHistory

ON Relationship.PKRelationship = PaymentHistory.FKRelationship

GROUP BYRelationship.PKRelationship, CellPhone.PhoneNumber

) as bon a.PKRelationship = b.PKRelationship

and a.PhoneNumber = b.PhoneNumber

AMB

KernProbation

Tested and this solution works, too!

Tested and this solution works, too!