How to aggregate with join

I am very new to SQL and I was wondering if someone could help me find the quantity on hand (dqty-sqty) for each iname? This is what I’ve been trying to do so far but the total quantity that I get is wrong:

select delivery.iname, sum(dqty-sqty) as "Quantity on Hand"    
from sale join delivery on    
sale.iname=delivery.iname    
group by delivery.iname    
order by delivery.iname;

Thanks so much!

Answer

you need to take the sum of the delivered and sold items individually as JOINs will not work directly on tables as there is many to many relationship between two tables.

Try this:

SELECT D.INAME, DQTY - SQTY AS "Quantity on Hand"
FROM
(SELECT DELIVERY.INAME, SUM(DQTY) DQTY
FROM DELIVERY
GROUP BY DELIVERY.INAME) D
LEFT JOIN
(SELECT SALE.INAME, SUM(SQTY) SQTY
FROM SALE
GROUP BY SALE.INAME) S
ON D.INAME = S.INAME;

I have used LEFT JOIN in case the item is not sold.