MessiahCoder MessiahCoder - 2 months ago 6
SQL Question

Sum columns from two tables in sql

I have two tables, one is the cost table and the other is the payment table, the cost table contains the cost of product with the product name.

Cost Table
id | cost | name
1 | 100 | A
2 | 200 | B
3 | 200 | A


Payment Table
pid | amount | costID
1 | 10 | 1
2 | 20 | 1
3 | 30 | 2
4 | 50 | 1


Now I have to sum the total of cost by the same
name
values, and as well sum the total amount of payments by the costID, like the query below

totalTable

name | sum(cost) | sum(amount) |
A | 300 | 80 |
B | 200 | 30 |


However I have been working my way around this using the query below but I think I am doing it very wrong.

SELECT
b.name,
b.sum(cost),
a.sum(amount)

FROM
`Payment Table` a

LEFT JOIN
`Cost Table` b
ON
b.id=a.costID


GROUP by b.name,a.costID


I would be grateful if somebody would help me with my queries or better still an idea as to how to go about it. Thank you

Answer

This should work:

select t2.name, sum(t2.cost), coalesce(sum(t1.amount), 0) as amount
from (
   select id, name, sum(cost) as cost
   from `Cost`
   group by id, name
) t2
left join (
   select costID, sum(amount) as amount
   from `Payment`
   group by CostID
) t1 on t2.id = t1.costID
group by t2.name

SQLFiddle