select inventory_item_master.name, CASE uom WHEN 'oz' Then size WHEN 'ml' Then 0.0338 * size WHEN 'L' Then 33.814 * size WHEN 'gal' Then 128 * size WHEN 'lbs' Then 16 * size WHEN 'each' Then size WHEN 'cnt' THEN size else 0 END as size, CASE 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 uom WHEN 'oz' Then size * ((sum(case_qty) * max(caseq)) + (sum(pack_qty) * max(packq)) + sum(qty) + sum(open_qty)) WHEN 'ml' Then (0.0338 * size) * ((sum(case_qty) * max(caseq)) + (sum(pack_qty) * max(packq)) + sum(qty) + sum(open_qty)) WHEN 'L' Then (size * 33.814) * ((sum(case_qty) * max(caseq)) + (sum(pack_qty) * max(packq)) + sum(qty) + sum(open_qty)) WHEN 'gal' Then (128 * size) * ((sum(case_qty) * max(caseq)) + (sum(pack_qty) * max(packq)) + sum(qty) + sum(open_qty)) WHEN 'lbs' Then (16 * size) * ((sum(case_qty) * max(caseq)) + (sum(pack_qty) * max(packq)) + sum(qty) + sum(open_qty)) WHEN 'each' Then size * ((sum(case_qty) * max(caseq)) + (sum(pack_qty) * max(packq)) + sum(qty) + sum(open_qty)) WHEN 'cnt' THEN size * ((sum(case_qty) * max(caseq)) + (sum(pack_qty) * max(packq)) + sum(qty) + sum(open_qty)) else 0 END as total_size, CASE 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, size as container_size, uom as container_uom 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 where date >= 000 and date <= 000 and item_group_master.inventory_type = '' and item_group_master.restaurant_type = '' Group by inventory_item_master.name, uom, size Order by inventory_item_master.name