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;
CodePudding user response：
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