## Solution

#### Approach: Using GROUP BY and COUNT [Accepted]

Intuition

To decide whether a value in a column is unique or not, we can use GROUP BY and COUNT.

Algorithm

Check whether the value of a record's TIV_2015 is unique, if it is not unique, and at the same time, its location (LAT, LON) pair is unique, then this record meeting the criteria. So it should be counted in the sum.

MySQL

SELECT
SUM(insurance.TIV_2016) AS TIV_2016
FROM
insurance
WHERE
insurance.TIV_2015 IN
(
SELECT
TIV_2015
FROM
insurance
GROUP BY TIV_2015
HAVING COUNT(*) > 1
)
AND CONCAT(LAT, LON) IN
(
SELECT
CONCAT(LAT, LON)
FROM
insurance
GROUP BY LAT , LON
HAVING COUNT(*) = 1
)
;


Tips: Concat the LAT and LON as a whole to represent the location information.

Note: These two criteria should be met without an order, so if you attempt to filter data using criteria #1 first and then criteria #2, you will get a wrong result.

Taking the sample input as an example, the data set will be as following after taking the first criteria.

PID TIV_2015 TIV_2016 LAT LON
1 10 5 10 10
3 10 30 20 20
4 10 40 40 40

Then, the second criteria cannot filter any records on this data set. So the result is 75(5+30+40), which is obviously wrong since the location of record with PID '3' is actually the same with the record having been filtered by the first criteria.

PID TIV_2015 TIV_2016 LAT LON
2 20 20 20 20
3 10 30 20 20