Kombination zweier Abfragen

Question

Diese erste Abfrage erfasst alle Transaktionsdetails für einen bestimmten Datumsbereich Dies ist jedoch knifflig, ich brauche eigentlich die Summe der Summen in dieser Abfrage, wenn das einen Sinn macht.

SELECT  td.transaction_id,
    sum(    IF( coalesce(ti.na, -1) = 0
            AND coalesce(ti.special_info_type, -1) = 0
            AND coalesce(ti.item_type, '') = 'P'
            AND coalesce(ti.comp_id, 0) <= 0,
            coalesce(disc_amt, 0),
            0
        )
    ) as disc_sum,

    sum(    IF( coalesce(ti.na, -1) = 0
            AND coalesce(ti.special_info_type, -1) = 0
            AND coalesce(ti.item_type, '') = 'P'
            AND coalesce(ti.comp_id, 0) > 0,
            coalesce(comp_amt, 0),
            0
        )
    ) as cSM,

    sum(    IF( coalesce(ti.na, -1) = 0
            AND coalesce(ti.special_info_type, -1) = 0
            AND coalesce(ti.item_type, '') = 'P'
            AND coalesce(ti.comp_id, 0) > 0,
            coalesce(comp_tax, 0),
            0
        )
    ) as cTX

FROM transaction_details td

LEFT OUTER JOIN transaction_items ti
    ON ti.transaction_id = td.transaction_id

WHERE   td.na = 0
    AND td.entry_TS >= ?
    AND td.entry_TS <  ?

GROUP BY td.transaction_id;

Diese Abfrage wird in einer Schleife für jede von der vorherigen Abfrage zurückgegebene Transaktion ausgeführt.

SELECT  count(x.id) as refCnt,
    coalesce(sum(x.item_price + x.sub_price), 0) as refAmt,
    coalesce(sum(x.efftax), 0) as refTax

from(
    SELECT  (tiP.item_price - tiP.comp_amt) as item_price,
        coalesce(sum(tiA.item_price), 0) as sub_price,
        (tiP.efftax - tiP.comp_tax) as efftax,
        tiP.id

    from transaction_items tiP

    left outer join transaction_items tiA
        on( tiP.id = tiA.ref_id
            and tiA.item_type = 'A'
            and tiA.na = 0
        ) 

    where   tiP.item_type = 'P'
        and tiP.na = 0
        and tiP.refund = 1
        #and tiP.transaction_id = 

    group by tiP.id
    order by tiP.transaction_id, tiP.order_id
) as x;

Answer 1

Zunächst können Sie Ihre COALESCE in die Abfrage ein.

NULL Werte tragen nicht zur SUMME in Ihrer Logik bei, deshalb können Sie sie frühzeitig filtern:

SELECT  td.transaction_id,
        SUM(IF(ti.comp_id < 0 OR ti.comp_id IS NULL, disc_amt, 0),
        SUM(IF(ti.comp_id > 0, comp_amt, 0),
        SUM(IF(ti.comp_id > 0, comp_tax, 0)
FROM    transaction_details td
LEFT OUTER JOIN
        transaction_items ti
ON      ti.transaction_id = td.transaction_id
WHERE   td.entry_TS >= ?
        AND td.entry_TS <  ?
        AND ti.na = 0
        AND ti.special_info_type = 0
        AND ti.item_type = 'P'
GROUP BY
        td.transaction_id;

Zweitens, die SUM ist kommutativ, d. h. die SUM de la SUM ist ein SUM der beitragenden Werte.

Sie können die Berechnung der Zwischenwerte überspringen SUM 's.

Answer 2

Versuchen Sie dies:

SELECT td.transaction_id, SUM(disc_sum)+SUM(cSM)+SUM(cTX)
FROM (
  SELECT SUM(...) As disc_sum, SUM(...) As cSM, SUM(...) As cTX
  FROM transaction_details td
  ....
)