combining two queries

Question

This first query grabs all the transaction_details for a particular date range This is tricky though, I actually need the sum of sums in this query if that makes any sense.

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;

This query is executed in a loop for each transaction returned from the previous query.

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

I would suggest placing your first query in a temporary table with 3 additional BLANK columns called refCnt, refAmt and refTax.

Then using those 3 extra columns in the temp table, update them using your second query.

Answer 2

Try this:

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

Answer 3

First, you can move your COALESCE's into the query.

NULL values do not contribute into the SUM in your logic, that's why you can filter them early:

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;

Second, the SUM is commutative, i. e. the SUM of the SUM's is a SUM of contributing values.

You may skip calculating the intermediate SUM's.