The result of the below code:
select a.pid, b.pid from Insurance a inner join Insurance b on (a.lat,a.lon) = (b.lat,b.lon)
where a.pid<>b.pid
is:
| pid | pid |
| --- | --- |
| 1 | 1 |
| 3 | 2 |
| 2 | 2 |
| 3 | 3 |
| 2 | 3 |
| 4 | 4 |
So when filtering the where a.pid<>b.pid
, we get the policyholder who has the unique location.
# Write your MySQL query statement below
select round(sum(tiv_2016), 2) tiv_2016 from Insurance
where pid not in (
select a.pid from Insurance a inner join Insurance b on (a.lat,a.lon) = (b.lat,b.lon)
where a.pid<>b.pid
)
and
tiv_2015 in (
select tiv_2015 from Insurance group by tiv_2015 having count(pid)>1
)