Генерация итога в цикле запроса

Question

У меня есть простой запрос, который вытягивает список записей по их идентификатору:

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

Затем я пытаюсь просмотреть эти идентификаторы в другом запросе другой таблицы, чтобы проверить наличие платежной записи. Цель состоит в том, чтобы затем суммировать все найденные записи о платежах, чтобы получить общую сумму платежей в долларах.

У меня есть этот запрос, чтобы сделать это, но он выдает ошибку: Не могу привести String [99.00] к значению типа [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)#

Что я делаю неправильно? Я на правильном пути?

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#

Answer 2

Как следует из сообщения об ошибке, вы используете строку, где ожидается массив. Другими словами, ArraySum() ожидает массив, но вы передаете строку. Технически столбец запроса - это скрытый массив, но, по-видимому, ArraySum не предназначен для такой обработки. В этом случае CF лечит resPaymentTotals.payment_amount в качестве ярлыка для 1-го значения в столбце payment_amount, которое является простым значением, а не массивом.

В то время как вы могли бы заставить текущий код работать, запросы внутри цикла очень неэффективны. Что вам действительно нужно, так это JOIN, а не петля. Чтобы подсчитать общее количество - на ticket_id - в одном запросе:

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

Если вам нужна общая сумма для всех билетов, просто опустите ticket_id в 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 3

Я бы просто использовал один запрос, и, как сказал Ageax, просто JOIN столы. Когда вы сделаете это, вам придется учитывать дублированные строки первичной таблицы, которые вы можете получить.

Для SQL:

SQL Fiddle

Эта установка предполагает MS SQL Server. Это можно игнорировать.

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 ) 
;

Запрос:

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

Это дает вам:

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

Итак, теперь я предполагаю, что вы будете что-то делать с этими ticket_ID; вероятно, просматривая их. Примечание: я бы переименовал запрос, чтобы описать данные, которые вы возвращаете. Мы изменили его определение.

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

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

Заметка ticket_id 3 и 4, и различия между их totalPamentAmount а также badPaymentAmount, У обоих есть NULL значение для платежа: 3 имеет явное NULL и 4 имеет неявное NULL так как нет соответствующих строк. Обычно, если вы добавите 1+NULL, ты получишь NULL, поскольку NULL не имеет значения; ты хочешь 1+0 вместо. SQL sum() будет учитывать явное NULL, но не для неявного NULL, Для неявных NULL, мы можем использовать coalesce() вернуть первый не NULL значение. Тогда мы можем к югу 0 за NULL и делать математику для totalPaymentAmount,