CODEWITHSUNDEEP
sqlsubquery
kristofer
kristofer

Reputation: 147

combining two queries

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;

Upvotes: 2

Views: 208

Answers (2)

Quassnoi
Quassnoi

Reputation: 425823

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.

Upvotes: 1

Lance Roberts
Lance Roberts

Reputation: 22840

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

Upvotes: 0

Related Questions