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)

can use pid instead of concatenate longitude and latitude :
select round(sum(TIV_2016), 2) as TIV_2016
from insurance
where TIV_2015 in (
select TIV_2015 from insurance group by TIV_2015 having count(1) > 1
) and pid in (
select max(pid) as id from insurance group by lat,lon having count(1) = 1
)
;
because in this case we are selecting the unique longitude/latitude.