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)


Log in to reply
 

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