Using GROUP BY and HAVING COUNT(*) to choose the (not) unique data.


  • 4

    Another trick is to represent the location information by concat the LAT and LON.

    SELECT SUM(insurance.TIV_2016) AS TIV_2016
    FROM insurance
    WHERE insurance.TIV_2015 IN -- meet the creteria #1
        (
           SELECT TIV_2015
            FROM insurance
            GROUP BY TIV_2015
            HAVING COUNT(*) > 1
            )
    AND CONCAT(LAT, LON) IN -- meet the creteria #2
        (
          SELECT CONCAT(LAT, LON) -- trick to take the LAT and LON as a pair
          FROM insurance
          GROUP BY LAT , LON
          HAVING COUNT(*) = 1
    )
    ;
    

Log in to reply
 

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