Investments in 2016
Problem
Insurance(pid, tiv_2015, tiv_2016, lat, lon). Sum tiv_2016 where tiv_2015 is shared with at least one other policyholder and (lat, lon) is unique.
ins=[(1,10,5,10,10),(2,20,20,20,20),(3,10,30,20,20),(4,10,40,40,0)]45SELECT 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)
AND (lat, lon) IN (SELECT lat, lon FROM Insurance GROUP BY lat, lon HAVING COUNT(*) = 1);Explanation
We sum tiv_2016, but only for rows that pass two filters at once: the 2015 value must be shared with someone else, and the location must be unique to that row.
The first condition uses a subquery that groups by tiv_2015 and keeps the values appearing more than once (HAVING COUNT(*) > 1). A row qualifies only if its tiv_2015 is in that "shared" set.
The second condition groups by (lat, lon) and keeps the location pairs that appear exactly once (HAVING COUNT(*) = 1). A row qualifies only if its coordinates are in that "unique location" set.
Rows satisfying both get their tiv_2016 added together, and ROUND(..., 2) formats the total.
Example: rows 1 and 4 both have tiv_2015 = 10 (shared) and each sits at a unique location, so we add 5 + 40 = 45. Row 3 shares its location with row 2, so it is excluded.