How can I SUM() the rows with no matching entry in another table?

Question

I sum the quantity of tbl_transaction_details and tbl_received_order_details. My problem is I can't get the one row which is no matching entry in another table. I want to get the 5 with received_details_id is equal to 3. Here are my query and table.

when i execute this query

SELECT 
 SUM(r.transact + f.received) Total 
FROM
 (SELECT received_order_details_id, SUM(quantity) transact
FROM 
 tbl_transaction_details 
GROUP BY 
 tbl_transaction_details.received_order_details_id) r 
LEFT JOIN 
 (SELECT received_order_details_id, SUM(quantity) received  
FROM 
 tbl_received_order_details 
GROUP BY 
 tbl_received_order_details.received_order_details_id) f 
ON 
 r.received_order_details_id = f.received_order_details_id

it produces result

Total
92

tbl_received_order_details

received_details_id  qty
1                    0   
2                    70    
3                    5   

tbl_transaction_details

id    received_details_id   qty   
1     1                     1     
2     2                     11   
3     2                     10 

I want this to happen

Total
97

Answer 1

If you just want the total quantity from all rows in both tables, I suggest using a union query here:

SELECT SUM(qty) AS Total
FROM
(
    SELECT qty FROM tbl_transaction_details
    UNION ALL
    SELECT qty FROM tbl_received_order_details
) t;

Your current approach does not give the results you expect because not every received_details_id from each table matches to one in the other table.

Note that you could also just sum the sums:

SELECT
    (SELECT SUM(qty) FROM tbl_transaction_details) +
    (SELECT SUM(qty) FROM tbl_received_order_details) AS Total;