difficult summing query

[jmartineau]

Hello,

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.Cu stomer_ID, COUNT (DISTINCT
(Deposit_Balanc e_Table.Account _Number)), Sum
(Deposit_Balanc e_Table.Balance )
FROM Product_Table, Deposit_Balance
WHERE (Product_Table. Account_Number= Deposit_Balance .Account_Number )
GROUP BY Product_Table.C ustomer_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.
Something to do with the handedness of my joins, I believe. Often I
will get one column of information (either deposits or loans), or the
query will fail because the join I'm attempting is invalid, etc. I need
to take every row in the All_Accounts table, match each one to its
balance in either the Deposit or Loan table, and then group them all by
the Customer ID and sum them, so that I can find out the total
relationship balance per customer. Any help would be appreciated.

[Erland Sommarskog]

(jm********@gma il.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.Cu stomer_ID, COUNT (DISTINCT
(Deposit_Balanc e_Table.Account _Number)), Sum
(Deposit_Balanc e_Table.Balance )
FROM Product_Table, Deposit_Balance
WHERE (Product_Table. Account_Number= Deposit_Balance .Account_Number )
GROUP BY Product_Table.C ustomer_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.DepositBa lance), SUM(b.LoanBalan ce)
FROM All_Accounts a
JOIN (SELECT Account#, DepositBalance, LoanBalance = 0
FROM DepositBalanceT able
UNION ALL
SELECT Account#, 0, LoanBalance
FROM LoanBalanceTabl e) 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, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[jmartineau]

Ah, this sort of thing is exactly what I need. However, when I modified
the query you gave to fit the particular table names, etc, I now have
an error: "Missing Keyword" at this point:

FROM LoanBalanceTabl e) AS b ON a.Account# = b.Account#
**

Do you know what would cause this? Thanks,

[Erland Sommarskog]

(jm********@gma il.com) writes:

Ah, this sort of thing is exactly what I need. However, when I modified
the query you gave to fit the particular table names, etc, I now have
an error: "Missing Keyword" at this point:

FROM LoanBalanceTabl e) AS b ON a.Account# = b.Account#
**

Do you know what would cause this? Thanks,

When I run the query:

SELECT a.CustomerID, SUM(b.DepositBa lance), SUM(b.LoanBalan ce)
FROM All_Accounts a
JOIN (SELECT Account#, DepositBalance, LoanBalance = 0
FROM DepositBalanceT able
UNION ALL
SELECT Account#, 0, LoanBalance
FROM LoanBalanceTabl e) AS b ON a.Account# = b.Account#
GROUP BY a.CustomerID

All I get is complaints on the tables missing.

But I don't recognize the error message "Missing keyword". Are you
really using Microsoft SQL Server?
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[jmartineau]

Nope, using Oracle9i. Does that change things? From what I could see
online, the "Missing keyword" error is a poorly defined syntax error of
some kind - something is/was wrong with my query and that is how it
told me so. However, I had modified the query in subtle ways to try to
make it work, so I'll backtrack a bit. Here's the query verbatim:

SELECT
AL1.INVOLVED_PA RTY_ID_PRIMARY| |','||SUM(B.Dep ositBalance)||' ,'||SUM(B.LoanB alance)||','
FROM INFORMENT.PRODU CT_OFFER_PURCHA SE AL1
JOIN (SELECT AL2.ACCOUNT_NUM BER, AL2.BAL_LEDGER_ CURRENT AS
DepositBalance, LoanBalance = 0
FROM INFORMENT.DEPOS IT_BALANCE AL2
UNION ALL
SELECT AL3.ACCOUNT_NUM BER, 0, AL3.BAL_PRIN AS LoanBalance
FROM INFORMENT.LOAN_ BALANCE AL3) AS B ON AL1.ACCOUNT_NUM BER =
B.ACCOUNT_NUMBE R
GROUP BY AL1.INVOLVED_PA RTY_ID_PRIMARY
ORDER BY 1;

This query is the closest I can get to mirroring your example code. It
produces a different error:

JOIN (SELECT AL2.ACCOUNT_NUM BER, AL2.BAL_LEDGER_ CURRENT AS
DepositBalance, LoanBalance = 0
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected

The only real changes I made to the example you gave were to format the
output as a csv, and to try to change some of the names around. It
seems to be getting angry at the = 0 part, not sure why. One thing I
made different was that I pulled the balances AS the names LoanBalance
and DepositBalance, since by default they'd be less clear. I assume
this should have no impact on the query. Not quite sure what's
incorrect. Any more thoughts?

Thanks very much for your help thus far.

[Erland Sommarskog]

(jm********@gma il.com) writes:

Nope, using Oracle9i. Does that change things?
Yes. You should post in comp.databases. oracle, to get more accurate
answers. In this newsgroup you will get syntax that works in Microsoft
SQL Server.

JOIN (SELECT AL2.ACCOUNT_NUM BER, AL2.BAL_LEDGER_ CURRENT AS
DepositBalance, LoanBalance = 0
*
ERROR at line 3:
ORA-00923: FROM keyword not found where expected

That syntax is indeed proprietary to MS SQL Server (and Sybase). I believe
that the ANSI way of writing this is "0 AS LoanBalance" or just "0
LoanBalance". Both syntaxes works on SQL Server, and may work in Oracle.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx