CODEWITHSUNDEEP
mysqlsql
Abdul Ali
Abdul Ali

Reputation: 1937

Taking sum from master and child table

I hope that I am able to explain the situation as much as possible :)

We need to take sum from Master and child records of MySQL tables. The current query is as follows:

select  sum(
 abs(ifnull(dt.N_RETAIL_PRICE,0 ) * ifnull(dt.N_QTY_NO ,0) )
 + ifnull(st.shipping_total,0 ) + ifnull(st.TaxAmount,0 ) - abs(ifnull(st.discount ,0))
)  Total

FROM inv_store_transaction st

inner join inv_store_transaction_det dt

on st.OID = dt.INV_STORE_TRANSACTION

where st.INV_TRANSACTION_TYPE = 35
and st.INV_STORES = 1

The issue what we suspect is that if the detail column has more than 1 row, the columns of master will be summed that many times.

e.g if detail has say 3 rows, then the sum of its relevant master data will also be taken 3 times.

To summarize, we need to take a grand total of all Invoices that fall under the given condition.

Any help appreciated.

Upvotes: 0

Views: 526

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The solution to this problem is to pre-aggregate the detail data:

select (sum(dt.amt) + sum((st.shipping_total) + sum(st.TaxAmount) - 
        sum(abs(st.discount))
       )  Total
FROM inv_store_transaction st inner join
     (select dt.INV_STORE_TRANSACTION,
             abs(coalesce(dt.N_RETAIL_PRICE, 0) * coalesce(dt.N_QTY_NO, 0)) as dtamt
      from inv_store_transaction_det dt
      group by dt.INV_STORE_TRANSACTION
     ) dt
     on st.OID = dt.INV_STORE_TRANSACTION
where st.INV_TRANSACTION_TYPE = 35 and st.INV_STORES = 1

You don't need to test for NULL unless all the rows have a NULL value for one of the columns.

Upvotes: 1

Related Questions