try this
create table items
(itemcode varchar(5),
stock_hand int,
product_Name varchar(2))
create table prod
(product_name varchar(2),
Productquan int,
checkbit bit,
itemcode varchar(5))
insert into items values ('P001',4,'T')
insert into items values ('P002',3,'P')
insert into items values ('P003',3,'L')
insert into prod values('T',1,0,'P001')
insert into prod values('P',2,1,'P002')
insert into prod values('L',1,0,'P003')
insert into prod values('P',3,1,'P002')
insert into prod values('L',2,1,'P003')
insert into prod values('L',2,0,'P003')
insert into prod values('T',3,1,'P001')
select p.itemcode,sum(stock_hand)+sum(productquan) from items i
inner join prod p on p.itemcode=i.itemcode
where checkbit=1
group by p.itemcode
the output will be
itemcode (No column name)
P001 7
P002 11
P003 5