Investments in 2016

You can write a query to calculate the sum of tiv_2016 for policyholders who meet the given criteria by using a subquery to filter the rows. Here’s the query:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM (
  SELECT tiv_2016
  FROM Insurance
  WHERE tiv_2015 IN (
    SELECT tiv_2015
    FROM Insurance
    GROUP BY tiv_2015
    HAVING COUNT(tiv_2015) > 1
  )
  AND CONCAT(lat, lon) IN (
    SELECT CONCAT(lat, lon)
    FROM Insurance
    GROUP BY lat, lon
    HAVING COUNT(CONCAT(lat, lon)) = 1
  )
) AS Subquery;

Explanation:

  • The first subquery selects tiv_2015 values that are repeated among policyholders, using GROUP BY and HAVING COUNT(tiv_2015) > 1.
  • The second subquery selects unique (lat, lon) pairs, using GROUP BY and HAVING COUNT(CONCAT(lat, lon)) = 1.
  • The main query filters rows based on these two subqueries, and then calculates the sum of tiv_2016, rounding to two decimal places using ROUND.