May 15, 2004 at 12:45 pm
Would someone please help me construct a query that would result in returning a single amount that is the total of the amount columns in two tables for a specified customer number?
Table #1: AR_Transactions
Table #2: AR _Post_Dates
Both tables have the following two columns:
Customer_Number
Amount (money)
I need to retrieve the total of the amount columns in both tables for a specified customer number.
Thank you,
Howard
May 16, 2004 at 11:33 am
Mike:
Superb...It works perfectly.
Thank you very much for the help.
Howard
May 19, 2004 at 10:04 am
Mike:
I just found a problem with your query:
select (select sum(Amount)
from AR_Transactions
where Customer_Number = @C) +
(select sum(Amount)
from AR_Post_Dates
where Customer_Number = @C)
If there are no entries in the AR_Post_Dates table but entries exist in the AR_Transaction table, the answer returned is null.
Is there a way to return the total for all three scenarios:
Entries in the AR_Transaction table but not entries in the AR_Post_Dates table.
No entries in the AR_Transaction table but entries in the AR_Post_Dates table.
Entries in both tables which currently works.
Thanks
Howard
May 19, 2004 at 10:57 am
Howard,
The problem is that NULL plus anything is NULL. You can use ISNULL (refer to the Books OnLine for more information).
select ISNULL((select sum(Amount)
from AR_Transactions
where Customer_Number = @C),0) +
ISNULL((select sum(Amount)
from AR_Post_Dates
where Customer_Number = @C),0)
I haven't tested it, but the syntax is correct. ISNULL will evaluate an expression (in this case it's the sub-SELECT) and if it returns NULL it will change it to 0. (In addition or subtraction you want to use 0, since 0 plus a number equals that number. In multiplication or division you want to use 1).
-SQLBill
May 19, 2004 at 11:04 am
Thank you. This will work just fine.
Howard
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply