Sum 1

[Gatorajc]

I have two tables.

<b>TableA</b>
CustID FName LName Credit
a12345 Joe Smith 20.00
a14568 Jane Doe Null
a56898 John Doe Null

<b>TableB</b>
OrderID CustID OrderTotal PaidTotal
15589 a12345 7.00 null
15658 a14568 5.00 17.00
15688 a56898 4.00 4.00

I want to be able to get the Credit from TableA(if there is one) and add it to whatever Paidtotal - OrderTotal.

Ive tried it a couple of ways:
This way it errors out.
Select sum((o.PaidTotal - rdertotal) + c.credit) as credit

This I get a null value.
Select sum((o.PaidTotal - rdertotal) + c.credit as Credit

Thanks in advance.


AJ


If at first you do not succeed, cheat!

 

[Gatorajc]

Sorry should have included the rest of it
FROM Table1 o join Table c on c.custid = o.custid
WHERE ((o.PaidTotal > rderTotal) or c.Credit > 0)

This is the result I want to get.
CustID Credit
a12345 20.00
a14568 12.00
a56898 0

AJ


If at first you do not succeed, cheat!

 

[rac2]

So you are interested in the folks who have a credit amount or in the folks who have made one or more payments in excess of the amount of their order but only in the excess amounts; do not add in any order amounts which are unpaid or partially paid?

The sum of the excess amounts for overpaid orders.

SELECT CustID, SUM(PaidTotal - Ordertotal) AS "Amount"
FROM Orders
WHERE PaidTotal > Ordertotal
GROUP BY CustID

For the moment we will pretend that query is saved as a VIEW named ExcessPaid.

Note that ExcessPaid will not include order 15688 by customer a56898 because the paid amount is not greater than the order amount. This matches your WHERE condition.


Now JOIN ExcessPaid and Customers for the rest of the calculation.

SELECT c.CustID,
       ISNULL(ep.Amount,0) + ISNULL(c.Credit, 0) AS "Credit"
FROM Customers c
LEFT JOIN ExcessPaid ep ON 
     c.CustID = ep.CustID

This should give you a row for all of the customers in the Customers table.

If you want to put it all in one query, replace the name ExcessPaid with the first query enclosed in parentheses.

Give it a whirl and let me know how it works.

 

[Gatorajc]

Thanks rac2,

I didnt use your suggestion directly. But you reminded me of the null values and they were screwing me up but I put in your ISNULL(c.Credit,0) and it works great.

Thanks again

AJ


If at first you do not succeed, cheat!