# Investments in 2016

• Click here to see the full article post

• 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)

• This doesn't work because in the following code:

``````SELECT PID
FROM insurance i1
GROUP BY i1.TIV_2015
HAVING count(PID) >1
``````

it will only return 1 PID for each group satisfying the condition, even if there are multiple PIDs in the group.

• Ah, I see. Thanks, @lleiou !

• 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 we use
select sum (tiv_2016)
from insurance a , insurance b
where a.pid<>b.pid
and a.tiv_2015= b.tiv_2015
and a.lat<>b.lat
and a.lon<>b.lon

• 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,100
lat+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,100
lat+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/investments-in-2016

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)

• select sum(TIV_2016) as TIV_2016
from insurance as I1
where TIV_2015 in (select TIV_2015
from insurance
group by TIV_2015
having count()>=2)
and
(select c from
(select LAT, LON, count(
) as c from insurance group by LAT, LON) as A
where I1.LAT = A.LAT and I1.LON = A.LON)=1

• This post is deleted!

• @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.

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.