Reputation: 161
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
Upvotes: 0
Views: 435
Reputation: 1271241
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;
Upvotes: 1