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

As the error message suggests, you're using a string where an array is expected. In other words, ArraySum() expects an array, but you're passing in a string. Technically a query column is an array behind the scenes, but apparently ArraySum isn't designed to treat it that way. In this case CF treats resPaymentTotals.payment_amount as shortcut for the 1st value in the payment_amount column, which is a simple value, not an array.

While you could make the current code work, querying within looping is very inefficient. What you really need is a JOIN, not a loop. To calculate the total - per ticket_id - in a single query:

<cfquery>
SELECT ct.ticket_id, SUM(p.payment_amount) AS TotalSales
FROM   closed_tickets ct LEFT JOIN payments p ON ct.ticket_id = p.ticket_id
WHERE  ct.closed_date >= '2017-01-01'
AND    ct.closed_date < '2018-01-01'
AND    ct.ticket_type = 'residential'
GROUP BY ct.ticket_id
</cfquery>

If you want the overall total for all tickets, just omit ticket_id from the SELECT.

<cfquery>
SELECT  SUM(p.payment_amount) AS TotalSales
FROM   closed_tickets ct LEFT JOIN payments p ON ct.ticket_id = p.ticket_id
WHERE  ct.closed_date >= '2017-01-01'
AND    ct.closed_date < '2018-01-01'
AND    ct.ticket_type = 'residential'
</cfquery>

Answer 2

I would just use one query, and like Ageax said, just JOIN the tables. When you do this, you'll have to account for the duplicated primary table rows that you may get.

For the SQL:

SQL Fiddle

This Setup is assuming MS SQL Server. It can be ignored.

CREATE TABLE closed_tickets ( 
      ticket_ID int identity
    , closed_date date
    , ticket_type varchar(50) 
) ;
INSERT INTO closed_tickets (closed_date, ticket_type)
VALUES 
      ('2017-01-01','residential')
    , ('2017-01-02','commercial')
    , ('2017-01-03','residential')
    , ('2017-07-01','residential') /* No Payments */
    , ('2018-02-01','residential')
;

CREATE TABLE payment_amount ( 
      payment_ID int identity
    , ticket_id int
    , amount decimal(12,2) 
) ;
INSERT INTO payment_amount(ticket_id, amount)
VALUES 
      ( 1, 100.50 )
    , ( 2, 50.00 ) 
    , ( 3, 50.00 ) 
    , ( 2, null ) 
    , ( 1, 10.00 ) 
    , ( 2, 0.50 ) 
    , ( 1, 20.00 ) 
    , ( 2, 75.00 ) 
    , ( 5, 500.00 )
    , ( 3, 5 ) 
    , ( 3, null ) 
    , ( 3, 25 ) 
;

The Query:

<cfquery name="resTotals" datasource="#datasource#">
    SELECT ct.ticket_ID
      , sum(COALESCE(pa.amount,0))  AS totalPaymentAmount
      , sum(pa.amount)  AS badPaymentAmount /* What about NULLs? */
    FROM closed_tickets ct
    LEFT OUTER JOIN payment_amount pa ON ct.ticket_ID = pa.ticket_ID
    WHERE ct.ticket_type = 'residential'
      AND year(ct.closed_date) = 2017   /* year() returns INT *//* And you lose index use */
    GROUP BY ct.ticket_ID
</cfquery>

This Gives You:

| ticket_ID | totalPaymentAmount | badPaymentAmount |
|-----------|--------------------|------------------|
|         1 |              130.5 |            130.5 |
|         3 |                 80 |               80 |
|         4 |                  0 |           (null) |

So now I assume you'll be doing something with those ticket_IDs; probably looping through them. Note: I'd rename the query to be descriptive of the data you're returning. We changed its definition.

<cfloop query="resTotals"> 
    [DO STUFF]

    Amount of Sales: $ #totalPaymentAmount)#
</cfloop>

Note ticket_id 3 and 4, and differences between their totalPamentAmount and badPaymentAmount. Both have a NULL value for a payment: 3 has an explicit NULL and 4 has an implicit NULL since there are no matching rows. Normally, if you add 1+NULL, you'll get NULL, since NULL has no value; you want 1+0 instead. SQL sum() will account for the explicit NULL, but not for an implicit NULL. For the implicit NULL, we can use coalesce() to return the first non-NULL value. Then we can sub 0 for NULL and do math for totalPaymentAmount.

Answer 3

<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#