Sum from multiple tables

[Rusty Jones]

I have three tables:
Accounts - store an id and information about the account.
External Transfer - stores an account id and the amount transfered into the
account
Internal Transfer - transfer from one account to another (from account, to
account, and amount)

I want a query that computes the balance in each account. The balance is
the sum of all the external transfers plus the internal transfers into the
account minus the internal transfers that go from the account.

Any time I try to build a query to do this I end up with some of the values
used twice.

Any ideas?

 

[John Spencer]

This looks as if you will need to use subqueries in the SELECT clause.

SELECT Accounts.AccountID
, SUM (Amount) as ExternalSum
, (SELECT Sum(Amount)
FROM InternalTransfer
WHERE InternalTransfer.FromAccount= Accounts.AccountID) as InternalOut
, (SELECT Sum(Amount)
FROM InternalTransfer
WHERE InternalTransfer.ToAccount= Accounts.AccountID) as InternalIn
FROM Accounts LEFT JOIN ExternalTransfer
ON Accounts.AccountID =ExternalTransfer.AccountID
GROUP BY Accounts.AccountID