SQL - MULTIPLE TBL SUMMING

[kilabru]

I have 4 tables of data. 3 tables link to the main table ITEM via itemnum
As the following depicts, I am wanting to take the three individual select statements and run them as a sum with the particular itemnum. In other words, I want it to look similar to the desired output:

ITEMNUM TOTAL
4730-00-D76-0569 15

----------------------------------------------------------
select it.itemnum, sum(inv.quantity) from item it, invtrans inv
where it.itemnum = inv.itemnum and
it.itemnum = '4730-00-D76-0569'
group by it.itemnum
---OUTPUT
8
----------------------------------------------------------
select it.itemnum, sum(matu.quantity) from item it, matusetrans matu
where it.itemnum = matu.itemnum and
it.itemnum = '4730-00-D76-0569'
group by it.itemnum
---OUTPUT
-10
----------------------------------------------------------
select it.itemnum, sum(matr.quantity) from item it, matrectrans matr
where it.itemnum = matr.itemnum and
it.itemnum = '4730-00-D76-0569'
group by it.itemnum
---OUTPUT
17
----------------------------------------------------------

 

[carp]

SELECT itemnum, sum(qty)
FROM
(select it.itemnum, inv.quantity qty
from item it, invtrans inv
where it.itemnum = inv.itemnum and
it.itemnum = '4730-00-D76-0569'
UNION
select it.itemnum, matu.quantity
from item it, matusetrans matu
where it.itemnum = matu.itemnum and
it.itemnum = '4730-00-D76-0569'
UNION
select it.itemnum, matr.quantity
from item it, matrectrans matr
where it.itemnum = matr.itemnum and
it.itemnum = '4730-00-D76-0569')
GROUP BY itemnum;

There are other ways and they may be more efficient. This was just the first one that came to mind.

 

[carp]

My goof!
The code SHOULD look like:
SELECT itemnum, sum(qty)
FROM
(select it.itemnum, inv.quantity qty
from item it, invtrans inv
where it.itemnum = inv.itemnum and
it.itemnum = '4730-00-D76-0569'
UNION ALL
select it.itemnum, matu.quantity
from item it, matusetrans matu
where it.itemnum = matu.itemnum and
it.itemnum = '4730-00-D76-0569'
UNION ALL
select it.itemnum, matr.quantity
from item it, matrectrans matr
where it.itemnum = matr.itemnum and
it.itemnum = '4730-00-D76-0569')
GROUP BY itemnum;

UNION would eliminate duplicate rows and require more sorts. UNION ALL doesn't do a sort and leaves duplicate rows alone.