Generating a total within a query loop

Question

I have a simple query that pulls a list of records by their ID:

<cfquery name="resTotals" datasource="#datasource#">
   SELECT ticket_id
   FROM   closed_tickets
   WHERE  YEAR(closed_date) = '2017' 
   AND    ticket_type = 'residential' 
</cfquery>

I am then trying to loop through those ID's in another query of a different table so I can check for the existence of a payment record. The goal is to then total up all the payment records find to get a total amount in dollar of payments.

I have this query to do that but it is throwing an error: Can't cast String [99.00] to a value of type [Array]

<cfloop query="resTotals">
     <cfquery name="resPaymentTotals" datasource="#datasource#">
        SELECT payment_amount
        FROM   payments
        WHERE  ticket_id = #resTotals.ticket_id#
     </cfquery>

 </cfloop>

 Amount of Sales: $ #ArraySum(resPaymentTotals.payment_amount)#

What am I doing wrong? Am I on the right track?

Answer 1

<cfset total = 0 >
<cfloop query="resTotals">
     <cfquery name="resPaymentTotals" datasource="#datasource#">
     select payment_amount
     from payments
     where ticket_id = #resTotals.ticket_id#
     </cfquery>
 <cfset total = total + resPaymentTotals.payment_amount >
 </cfloop>
 Amount of Sales: $ #total#