The first answer is close to correct.
Infact, the requirement is to change all integer RefIDs to “US”, and then extract the country code from all other RefIDs.
Here is the correct method:
SELECT ID, COUNT(DISTINCT CASE WHEN RefID REGEXP '^[0-9]+$' THEN 'US' ELSE SUBSTRING(RefID, 5, 2) END ) CountryCount FROM TABLE GROUP BY ID ORDER BY CountryCount DESC;
CLICK HERE to find out more related problems solutions.