Posted by Erland Sommarskog on 11/23/05 00:22

(jmartineau@gmail.com) writes:
> Here is a brief summary:
>
> Table 1 = All Accounts
> - with fields such as Customer ID and Account #
>
> Table 2 = Deposit Balance Table
> - with fields such as Account #, Balance
>
> Table 3 = Loan Balance Table
> - with fields such as Account #, Balance
>
> All accounts are either deposit accounts or loan accounts. What I need
> to do is to gather information about total balances in both deposits
> and loans for each customer. I haven't been able to hit the right query
> for doing this. I can easily get information about one or the other,
> such as the following:
>
> SELECT All_Accounts.Customer_ID, COUNT (DISTINCT
> (Deposit_Balance_Table.Account_Number)), Sum
> (Deposit_Balance_Table.Balance)
> FROM Product_Table, Deposit_Balance
> WHERE (Product_Table.Account_Number=Deposit_Balance.Account_Number)
> GROUP BY Product_Table.Customer_ID ORDER BY 1
>
> Which will give me one row for each user, and show me the total number
> of deposit accounts each customer has and a sum of the balances in each
> of those accounts. I can make a similar query involving Loan Accounts.
> As soon as I try to draw both, however, I wind up below my depth.

SELECT a.CustomerID, SUM(b.DepositBalance), SUM(b.LoanBalance)
FROM All_Accounts a
JOIN (SELECT Account#, DepositBalance, LoanBalance = 0
FROM DepositBalanceTable
UNION ALL
SELECT Account#, 0, LoanBalance
FROM LoanBalanceTable) AS b ON a.Account# = b.Account#
GROUP BY a.CustomerID

The thing in parentheses is a derived table. Conceptually a temp
table within the query, but it is never materialized. Often a very
powerful tool to tackle complicated problems.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]