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