Investments in 2016
Task: Report the sum of all total investment values in 2016 (tiv_2016), for all policyholders who have the same tiv_2015 value as one or more other policyholders, and are not located in the same city as any other policyholder.
Table: Insurance
+-------------+-------+
| Column Name | Type |
+-------------+-------+
| pid | INT |
| tiv_2015 | float |
| tiv_2016 | float |
| lat | float |
| lon | float |
+-------------+-------+Input: Insurance table:
+-----+----------+----------+-----+-----+
| pid | tiv_2015 | tiv_2016 | lat | lon |
+-----+----------+----------+-----+-----+
| 1 | 10 | 5 | 10 | 10 |
| 2 | 20 | 20 | 20 | 20 |
| 3 | 10 | 30 | 20 | 20 |
| 4 | 10 | 40 | 40 | 40 |
+-----+----------+----------+-----+-----+Output:
+----------+
| tiv_2016 |
+----------+
| 45 |
+----------+View Reference Solution & Explanation ▼
This solution uses the `GROUP BY` clause to aggregate results, a `HAVING` clause to filter the aggregated groups, the `ROUND()` function to format numerical output, and a subquery to filter records based on another query's result.
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) AND (lat, lon) IN (SELECT lat, lon FROM Insurance GROUP BY lat, lon HAVING COUNT(*) = 1);