SQL subquery (stockin quantity runtime by sum up the stock in and stock out group by store and than subtract from stockin to stock out)

Question

I have 2 tables stockin and stockout i want to calculate stockin quantity runtime by sum up the stock in and stock out group by store and than subtract from stockin to stock out .. My query is working well but when it doesnt found any record in stock out table it do some unusual calculation

Select 
CASE 
    WHEN 
    (select ISNULL(Items_store.Item_ID,0) from Items_Store where Items_Store.Store_ID = Inventory_Incoming.Store_ID)
    <> 0
    THEN   
    SUM(Quentity)- 
    (select SUM(Items_Out) from Items_Store where Items_Store.Store_ID = Inventory_Incoming.Store_ID)
    ELSE
    SUM(Quentity) 
END as Stock 
,Store_ID,Item_ID
from Inventory_Incoming 
where Item_ID =1

group by 
Store_ID,
Item_ID

Answer 1

This

(select ISNULL(Items_store.Item_ID,0) from Items_Store where ...)

gets you the Item_ID from Items_Store. In case it is null (and I suppose this is never the case) you replace it by 0. In case no record can be found, you get NULL.

Replace this by

ISNULL((select Items_store.Item_ID from Items_Store where ...), 0)

But as Gordon already mentioned, better simplify your query.

EDIT: It seems you are not using all criteria when linking the tables. See here:

select 
  sum(quentity) - 
  coalesce(
  (
    select sum(items_out) 
    from items_store is
    where is.store_id = ii.store_id and is.item_id = ii.item_id
  ), 0)
from inventory_incoming ii
where item_id =1
group by store_id, item_id;

items_store must be linked by both store_id and item_id.