select inventory_item_master.name, CASE item_detail_master.uom WHEN 'oz' Then item_detail_master.size WHEN 'ml' Then 0.0338 * item_detail_master.size WHEN 'L' Then 33.814 * item_detail_master.size WHEN 'gal' Then 128 * item_detail_master.size WHEN 'lbs' Then 16 * item_detail_master.size WHEN 'each' Then item_detail_master.size WHEN 'cnt' THEN item_detail_master.size else 0 END as size, CASE item_detail_master.uom WHEN 'oz' Then 'oz' WHEN 'ml' Then 'oz' WHEN 'L' Then 'oz' WHEN 'gal' Then 'oz' WHEN 'lbs' Then 'oz' WHEN 'each' Then 'each' WHEN 'cnt' Then 'cnt' else 'n/a' END as uom, avg(item_detail_master.container_cost) as cost, (sum(case_qty) * max(caseq)) + (sum(pack_qty) * max(packq)) + sum(qty) + sum(open_qty) as total_count, avg(container_cost) * ((sum(case_qty) * max(caseq)) + (sum(pack_qty) * max(packq)) + sum(qty) + sum(open_qty)) as total_cost, CASE item_detail_master.uom WHEN 'oz' Then item_detail_master.size * ((sum(case_qty) * max(caseq)) + (sum(pack_qty) * max(packq)) + sum(qty) + sum(open_qty)) WHEN 'ml' Then (0.0338 * item_detail_master.size) * ((sum(case_qty) * max(caseq)) + (sum(pack_qty) * max(packq)) + sum(qty) + sum(open_qty)) WHEN 'L' Then (item_detail_master.size * 33.814) * ((sum(case_qty) * max(caseq)) + (sum(pack_qty) * max(packq)) + sum(qty) + sum(open_qty)) WHEN 'gal' Then (128 * item_detail_master.size) * ((sum(case_qty) * max(caseq)) + (sum(pack_qty) * max(packq)) + sum(qty) + sum(open_qty)) WHEN 'lbs' Then (16 * item_detail_master.size) * ((sum(case_qty) * max(caseq)) + (sum(pack_qty) * max(packq)) + sum(qty) + sum(open_qty)) WHEN 'each' Then item_detail_master.size * ((sum(case_qty) * max(caseq)) + (sum(pack_qty) * max(packq)) + sum(qty) + sum(open_qty)) WHEN 'cnt' THEN item_detail_master.size * ((sum(case_qty) * max(caseq)) + (sum(pack_qty) * max(packq)) + sum(qty) + sum(open_qty)) else 0 END as total_size, CASE item_detail_master.uom WHEN 'oz' Then 'oz' WHEN 'ml' Then 'oz' WHEN 'L' Then 'oz' WHEN 'gal' Then 'oz' WHEN 'lbs' Then 'oz' WHEN 'each' Then 'each' WHEN 'cnt' Then 'cnt' else 'n/a' END as total_uom, item_detail_master.size as container_size, item_detail_master.uom as container_uom, inventory_item_master.item_detail_id, par, distributor_master.name, max(distributor_item_master.bts) from inventory_master INNER JOIN inventory_item_master ON inventory_master.id = inventory_item_master.inventory_id INNER JOIN item_detail_master on item_detail_master.id = inventory_item_master.item_detail_id INNER JOIN item_group_master ON item_detail_master.item_group_id = item_group_master.id left outer join distributor_item_master on distributor_item_master.item_detail_id = item_detail_master.id left outer join distributor_master on distributor_item_master.distributor_id = distributor_master.id and distributor_master.ets = 0 where date >= 000 and date <= 000 and item_group_master.inventory_type = '' and item_group_master.restaurant_type = '' Group by inventory_item_master.name, item_detail_master.uom, item_detail_master.size, inventory_item_master.item_detail_id, par, distributor_master.name order by inventory_item_master.name