Investments in 2016


Can we not use the concat function and just filter the PID instead?
SELECT sum(i3.TIV_2016) as TIV_2016
FROM insurance i3
WHERE
PID in (SELECT PID
FROM insurance i1
GROUP BY i1.TIV_2015
HAVING count(PID) >1 )
AND
PID in (SELECT PID
FROM insurance i2
GROUP BY LAT, LON
HAVING count(PID) = 1)


Also, we can use fact the Lat and Lon are NUMERIC(5,2). Therefore we can hash them as 10000*Lat+Lon .
And the solution will be:select
sum(TIV_2016) as TIV_2016
from insurance i1
where
i1.TIV_2015 in (select TIV_2015 from insurance i2 where i2.PID!=i1.pid ) and
(i1.LAT10000 + i1.LON) not in (select (i3.LAT10000+ i3.LON) from insurance i3 where i3.PID!=i1.pid)