select Ind,ID,itemGrp.group_name as Name,SUM(Quantity) as Quantity, SUM(Subtotal) as Value from $trans_db.sales_invoice_details det inner join (select ItemGrp1.group_name, Item.item_id, 'G' as Ind,ItemGrp1.group_id as ID from $master_db.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 $master_db.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 $master_db.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, Item2.Item_id, 'I' as ind,Item2.Item_id as ID from $master_db.sales_inventory_items Item2 where Item2.group_id = '$f1') itemGrp on det.inventory_item_id = itemGrp.item_id where det.Voucher_no in ( SELECT Voucher_No from $trans_db.Sales_Invoice_Main main where str_to_Date(concat(main.voucher_date, substr(timestamp, 11,19)) ,'%Y-%m-%d %H:%i:%s') >= str_to_date('$fromDateTime','%Y-%m-%d %H:%i:%s')and str_to_Date(concat(main.voucher_date, substr(timestamp, 11,19)) ,'%Y-%m-%d %H:%i:%s') <=str_to_date('$toDateTime','%Y-%m-%d %H:%i:%s') ) group by itemGrp.group_name ORDER BY 1,2