how to make a complex count of unique queries?

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;