How to select result which calculates difference between two tables then display it along a third table?

Question

Regarding db fiddle

Table: items

id	name
1	bolt
2	wrench
3	hammer

Table: inwards

id	item_id	quantity
1	1	10.00
2	2	8.00

Table outwards

id	item_id	quantity
1	1	5.00

I want to SELECT table items with a third column AS balance which gets the difference between quantities (inwards - outwards)

My attempt:

SELECT it.* ,
(
(SELECT SUM(i.quantity)
FROM inwards AS i
WHERE i.item_id = it.id)
-
(SELECT SUM(o.quantity)
FROM outwards AS o
WHERE o.item_id = it.id)

) AS balance

FROM `items` AS it ORDER BY `id` ASC;

The result I get:

id	name	balance
1	bolt	5.00
2	wrench	null
3	hammer	null

What I am aiming to get:

id	name	balance
1	bolt	5.00
2	wrench	8.00
3	hammer	0.00

Answer 1

You could have coalesce()ed your two sums which would prevent the nulls in the output. Check into null propagation to understand why that was happening. This can be fixed inside the subquery as coalesce(sum(quantity), 0)

You might also consider this approach which might give you better flexibility overall if other data columns become relevant:

with combined as (
    select item_id,  quantity from inwards union all
    select item_id, -quantity from outwards
)
select i.item_id, min(name) as name, coalesce(sum(quantity), 0) as balance
from items i left outer join combined c on c.item_id = i.item_id
group by i.item_id;

Answer 2

Databases can't calculate with NULL, so you need to use something like COALESCE to check if the value is NULL and return a defualt valuze in your case 0

SELECT it.* ,
(
COALESCE((SELECT SUM(i.quantity)
FROM inwards AS i
WHERE i.item_id = it.id),0)
-
COALESCE((SELECT SUM(o.quantity)
FROM outwards AS o
WHERE o.item_id = it.id),0)

) AS balance

FROM `items` AS it ORDER BY `id` ASC;

id	name	balance
1	bolt	5.00
2	wrench	8.00
3	hammer	0.00

fiddle

Answer 3

select it.*, ifnull(t.sum_quantity,0) as quantity
from items it
left join 
(
    select item_id,sum(quantity ) as sum_quantity from (
        select item_id, quantity from inwards
        union all 
        select item_id, -quantity as quantity from outwards
    ) u group by item_id
) t on it.id=t.item_id