By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,461 Members | 1,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,461 IT Pros & Developers. It's quick & easy.

difficult summing query

P: n/a
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.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.Acco unt_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.
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.

Nov 23 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
(jm********@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.Acco unt_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, es****@sommarskog.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
Nov 23 '05 #2

P: n/a
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 LoanBalanceTable) AS b ON a.Account# = b.Account#
**

Do you know what would cause this? Thanks,

Nov 28 '05 #3

P: n/a
(jm********@gmail.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 LoanBalanceTable) 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.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

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****@sommarskog.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
Nov 28 '05 #4

P: n/a
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_PARTY_ID_PRIMARY||','||SUM(B.DepositB alance)||','||SUM(B.LoanBalance)||','
FROM INFORMENT.PRODUCT_OFFER_PURCHASE AL1
JOIN (SELECT AL2.ACCOUNT_NUMBER, AL2.BAL_LEDGER_CURRENT AS
DepositBalance, LoanBalance = 0
FROM INFORMENT.DEPOSIT_BALANCE AL2
UNION ALL
SELECT AL3.ACCOUNT_NUMBER, 0, AL3.BAL_PRIN AS LoanBalance
FROM INFORMENT.LOAN_BALANCE AL3) AS B ON AL1.ACCOUNT_NUMBER =
B.ACCOUNT_NUMBER
GROUP BY AL1.INVOLVED_PARTY_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_NUMBER, 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.

Nov 29 '05 #5

P: n/a
(jm********@gmail.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_NUMBER, 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****@sommarskog.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
Nov 29 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.