Summing Dollar Amounts from Query- Evaluate? Loop? CFSET? Let SQL DO IT?

the sum and average of to do list google drive sync/ to-do list spread sheet

I would still let the database do it. That's what it's for. If you have access to the database. This looks perfect for a stored procedure.

Do those payment amounts come from different tables? If they do, use a COALESCE(paymentX,0) for the specific field, and it will properly do the math. With a LEFT JOIN, you can get a NULL value, and you can't do math on a NULL. So your result becomes NULL.

Because you're using SQL 2008, you can also do a Common Table Expression and do a simple join from there.

Unfortunately, without more context of the data structure, I can't give you a better answer.

But looking at your cfoutput, I'll make some assumptions to build up a query.

Order_ fields are Order table.

Payment_ fields are Payment table. Payment is linked to Order and Client.    paymentAmount+shippingAmount == grandTotal  <<< my assumption here is that this is the grandTotal for the Payment. If it's supposed to be a Grand Total for the entire Order or for the Client, you'll have to get a bit more involved and GROUP these together, which will completely change your JOINs.

Client_ is Client table. Client is linked to Order and Payment.

Appraiser_ is Appraiser table. Appraiser is linked to Order.

One word of caution, when using OUTER JOINs, be very careful how you join them together. You may not be getting the results you think you're getting. Especially when you start adding functions into the mix. Keep all of the criteria for the JOIN inside the JOIN rather than part of it in the WHERE clause. See my example below for the Client table.

My example:

SELECT o.number as Order_Number, o.ID as Order_ID, o.Property_Street as Order_Property_Street, o.Property_City as Order_Property_City

     , p.ID as Payment_ID, p.date as Payment_Date, p.checknum as Payment_CheckNum

          , (COALESCE(p.paymentAmount,0) + COALESCE(p.ShippingAmount,0)) as Payment_GrandTotal

     , c.company as Client_Company

     , a.lname as Appraiser_LName

FROM Order o

LEFT OUTER JOIN Payment p ON p.OrderID = o.ID

LEFT OUTER JOIN Client ON c.OrderID = o.ID

     AND c.PaymentID = p.ID

LEFT OUTER JOIN Appraiser a ON a.OrderID = o.ID

EDIT:

And I just noticed that this question was from January, so I'm guessing you've likely already got a solution. However, if anyone else has a similar problem, I would still suggest dealing with it in the database. And again, without having more knowledge of the database schema and how those things relate to each other, or your business rules, I can't do much better with that query. And it could very well change it quite a bit. If the GrandTotal isn't for the Payment, is it for the entire Order or for the entire Client?

seasonedweb wrote: <cfoutput> <cfif #get_payments.payment_subtotal# IS ""> <cfset get_payments.payment_subtotal=0> <cfelseif  #get_payments.payment_add# IS ""> <cfset get_payments.payment_add=0> <cfelseif  #get_payments.payment_subtract# IS ""> <cfset get_payments.payment_subtract=0> </cfif> </cfoutput>

You could improve on this logic. It picks just one of the conditions, whereas it has to consider them all, separately.

Correct is:

<cfoutput query="get_payments">

<cfif payment_subtotal IS "">

    <cfset payment_subtotal=0>

</cfif>

<cfif payment_add IS "">

    <cfset payment_add=0>

</cfif>

<cfif payment_subtract IS "">

    <cfset payment_subtract=0>

</cfif>

<tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('d9ecff'))#">

etc.

</tr>

</cfoutput>