SELECT A.ro_inventory_item_id RO_INVENTORY_ITEM_ID,
A.TOTAL_RO_COUNT TOTAL_RO_COUNT,
b.diagnostic_code_id,
b.diagnostic_item_id,
count(C.repair_line_id) DC_ONLY_RO_COUNT
FROM CSD_RO_PER_ITEM_MV A,
CSD.CSD_RO_DIAGNOSTIC_CODES B,
CSD.CSD_REPAIR_ORDERS_F c
WHERE B.repair_line_id = C.repair_line_id
AND C.status = 'C'
AND A.ro_inventory_item_id = C.inventory_item_id
GROUP BY A.ro_inventory_item_id,
A.TOTAL_RO_COUNT,
b.diagnostic_code_id,
b.diagnostic_item_id