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.

Can someone explain why this query is wrong?
SELECT SUM(A.TIV_2016) AS 'TIV_2016'
FROM insurance as A
WHERE
(A.TIV_2015 IN (SELECT TIV_2015 FROM insurance WHERE A.PID <> PID))
AND
((A.LAT NOT IN (SELECT LAT FROM insurance WHERE A.PID <> PID))
OR
(A.LON NOT IN (SELECT LON FROM insurance WHERE A.PID <> PID)))

can anyone help me explain why my query is wrong? Many thanks!
select F.value as TIV_2016
from (
select count() as counts,sum(temp.TIV_2016) as value
from (
select PID,lat,lon,100lat+lon as location,TIV_2015,TIV_2016
from insurance
group by location
having count(*)=1 ) as temp
group by temp.TIV_2015
) as F
where F.counts>1

Can anyone help me why my query does not work? thank you
select F.value as TIV_2016
from (
select count() as counts,sum(temp.TIV_2016) as value
from (
select PID,lat,lon,100lat+lon as location,TIV_2015,TIV_2016
from insurance
group by location
having count(*)=1 ) as temp
group by temp.TIV_2015
) as F
where F.counts>1

CONCAT(LAT, LON) may not be right.
e.g
11, 2
1, 12
Maybe we do this?Blog link: https://brain.dennyzhang.com/investmentsin2016
select sum(TIV_2016) as TIV_2016
from insurance
where concat(LAT, ',', LON)
in (select concat(LAT, ',', LON)
from insurance
group by LAT, LON
having count(1) = 1)
and TIV_2015 in
(select TIV_2015
from insurance
group by TIV_2015
having count(1)>1)

@irtazasafi (LAT not in LATs) or (LON not in LONs) is very pessimist. In other words they might be equal to some others individually. should consider them together.