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.


Log in to reply
 

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