Home > Mobile >  Finding Percentage of Unmatching Records
Finding Percentage of Unmatching Records

Time:03-27

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
  •  Tags:  
  • sql
  • Related