SELECT Ind, Id, Name, Opening, Inward, Issued, ClosingStock as 'Closing Stock', StockValue 'Stock Value' from ( select ItemGrp.Ind, ItemGrp.Id, Name,    cast(sum(Opening) as decimal(10,3)) as Opening,   cast(sum(Inward) as decimal(10,3)) as Inward,   cast(sum(Issue) as decimal(10,3)) as Issued ,   cast(sum(Opening)+sum(Inward)-sum(Issue) as decimal(10,3)) AS 'ClosingStock',   cast(sum(Price) as decimal(10,3)) as 'StockValue' from   ( select  item_id,  (case when vdate >= str_to_date('$fromDateTime','%Y-%m-%d') and vdate <= str_to_date('$toDateTime','%Y-%m-%d') then (creditQty) else 0 end )as Issue,  (case when vdate >= str_to_date('$fromDateTime','%Y-%m-%d') and vdate <= str_to_date('$toDateTime','%Y-%m-%d') then (debitQty)  else 0 end )as Inward,  (case when vdate <  str_to_date('$fromDateTime','%Y-%m-%d')  or vdate is null then (debitQty) - (creditQty)  else 0 end) as Opening,  cr_amount, dr_amount,  coalesce((SELECT Price from Purchase_Invoice_Details where Inventory_Item_ID= item_ID order by Voucher_Date desc limit 1), (select std_cost FROM Sales_Inventory_Items where item_id = A.item_id)) * (debitQty - creditQty) as Price  from   (  select trans.VoucherDate vDate, ItemID as item_id,  0 debitQty , (trans.Cr_Qty) creditQty,  trans.Dr_Amount , trans.Cr_Amount, 'FROM' as type   from Transaction_Item_Helper trans where (1=1)  and VoucherDate <= str_to_date('2020-11-19','%Y-%m-%d') and (select isStockItem FROM Sales_Inventory_Items where itemID = item_ID) = 1  and From_Location like '$f2'  and To_Location not like 'OUT'  UNION ALL  select trans.VoucherDate, ItemID,  (trans.Dr_Qty) , 0,  trans.Dr_Amount , trans.Cr_Amount, 'TO' as type   from Transaction_Item_Helper trans where  (1=1)  and To_Location like '$f2'  and From_Location  like 'OUT'  and VoucherDate <= str_to_date('$fromDateTime','%Y-%m-%d') and (select isStockItem FROM Sales_Inventory_Items where itemID = item_ID) = 1  UNION ALL select trans.VoucherDate, ItemID as item_id,  0 , (trans.Cr_Qty),  trans.Dr_Amount , trans.Cr_Amount, 'FROM' as type   from Transaction_Bom_Helper trans where (1=1)  and VoucherDate <= str_to_date('$fromDateTime','%Y-%m-%d') and (select isStockItem FROM Sales_Inventory_Items where item_ID = itemID) = 1  and From_Location like '$f2'  and To_Location  like 'OUT'  UNION ALL  select trans.VoucherDate, ItemID,  (trans.Dr_Qty) , 0,  trans.Dr_Amount , trans.Cr_Amount, 'TO' as type   from Transaction_Bom_Helper trans where  (1=1)  and To_Location like '$f2'  and From_Location  like 'OUT'  and VoucherDate <= str_to_date('$fromDateTime','%Y-%m-%d') and (select isStockItem FROM Sales_Inventory_Items where item_ID = itemID) = 1  ) A where item_id is not null ) B  right outer join  (select ItemGrp1.group_name as Name, Item.item_id, 'G' as Ind, ItemGrp1.group_id as Id from  Sales_Inventory_Items item inner join  (with recursive cte (group_id, group_name, parent_id, item_id) as (  select     grp.group_id,grp.group_name,grp.parent_id, item.item_id  FROM Sales_Inventory_Items item left outer join Sales_Inventory_Groups grp   on item.group_id = grp.group_id  union all   select     p.group_id, p.group_name,p.parent_id, item_id  from       Sales_Inventory_Groups p   inner join cte   on p.group_id = cte.parent_id ) select cte.group_name, cte.item_id, cte.group_id  from cte where cte.parent_id = '$f1' ) itemGrp1  on itemGrp1.item_id = item.item_id  union  select Item2.Item_name as Name, Item2.Item_id, 'I' as ind, Item2.Item_Id as Id from  Sales_Inventory_Items Item2  where Item2.group_id = '$f1' ) ItemGrp on B.Item_Id = ItemGrp.Item_Id   group by Name ) X $f3 order by Name