MYSQL/PHP sum with group by

Question

tableA
id B_id C_id
1 1        0
2 1        0
3 0        1
4 0        2
5 0        2
6 0        2
tableB
id amount quantity
1   10         2

tableC
id amount quantity
1   6           1
2  15          3

I have this kind of database and I know it is not structured very well because I only continued this website and I wasn't given much time to restructure the website.

My question is how can i get the total amount of tableB and tableC using a LEFT JOIN of the two tables to tableA. As you can see the quantity in tableB and tableC will make the same number of tableA record. I was able to get each of the transactions amount using this code:

SELECT * FROM tableA A LEFT JOIN tableB B ON A.id = B.id LEFT JOIN tableC C ON C.id = A.id GROUP BY B.id, C.id

It would return:
id B_id C_id B.id B.amount B. quantity C.id C.amount C.quantity 1 1 0 1 10 2 0 0 0
3 0 1 0 0 0 1 6 1 4 0 2 0 0 0 2 15 3

but now I want to get the total using mysql SUM but GROUP BY cannot be used with the aggregate function SUM so the total should be: 10+6+15 = 31

Answer 1

Is this what you want?

SELECT coalesce(sum(b.quantity), 0) + coalesce(sum(c.quantity), 0)
FROM tableA A LEFT JOIN
     tableB B
     ON A.id = B.id LEFT JOIN
     tableC C
     ON C.id = A.id;

This returns the total across the two tables.

EDIT:

If you just want the sum of tables b and c, what is table a for? Is this what you want?

select amountb + amountc
from (select sum(amount) as amountb from tableb) b cross join
     (select sum(amount) as amountc from tablec) c;