Finding Percentage of Unmatching Records

There are 2 tables, hometown (showing the hometown) and residence (showing the places that the residents live in the past 10 years). I want to find the percentage of the residents that lived or is living out of there hometown. A resident can live in multiple places, and the state_of_residence can be duplicated; as long as there is a record that shows he/she lives in a state other than his/her hometown, it should be counted.

resident_id	hometown_state
1	ny
2	ma
3	ct
4	pa
5	vt

resident_id	state_of_residence
1	ny
1	ct
1	ny
2	ma
3	ca
4	wa
4	tx
5	vt

The query should return 60% since resident 1, 3, and 4 have one or more state of residence other than his/her hometown. The query I’m having isn’t return distinct state of residence, and putting DISTINCT inside a CASE statement return a syntax error. Much appreciated!

SELECT  ROUND((SUM(CASE WHEN r.state_of_residence != h.hometown_state
        THEN 1 ELSE 0 END)/COUNT(DISTINCT h.resident_id))*100,10)
FROM hometown h INNER JOIN residence r
ON h.resident_id=r.resident_id;

Answer

You can try to use COUNT condition aggregate function with DISTINCT instead of SUM aggregate function

SELECT COUNT(DISTINCT CASE WHEN r.state_of_residence <> h.hometown_state THEN h.hometown_state END) * 1.0
        / COUNT(DISTINCT h.resident_id) * 100
FROM hometown h 
INNER JOIN residence r
ON h.resident_id=r.resident_id
GROUP BY r.resident_id