Investments in 2016


  • 0

    Click here to see the full article post


  • 0
    C

    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)


  • 0
    L

    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.


  • 0
    C

    Ah, I see. Thanks, @lleiou !


  • 0
    G

    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)


  • 0
    C

    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


  • 0
    L

    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.


  • 0
    I

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


  • 0
    J

    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


  • 0
    J

    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


  • 0

    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)


  • 0
    E

    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


  • 0
    E
    This post is deleted!

  • 0
    E

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


Log in to reply
 

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