sqlnoob sqlnoob - 1 month ago 6
MySQL Question

SQL query with multiple conditions

I am stuck on this question. Can anyone help?

Write a query to print the sum of all total investment values in 2015 (TIV_2012) toa scale of 2 decimal places, for all policyholders who meet the following criteria:

1) Have the same TIV_2011 value as one or more other policyholders.

2) Are not located in the same city as another policy holder (i.e. (latitude, longitude) attribute pair must be unique,

the input format islike this , The table is

Insurance table is described as follows:

Column Name Type
PID INTEGER
TIV_2011 NUMERIC
TIV_2012 NUMERIC
LAT NUMERIC
LON NUMERIC

where PID is the policyholder's policy ID, TIV_2011 is the total investment in 2011,TIV_2012 is the total investment in 2012, LAT is the latitude of the policy holder's city and LON is the longitude of the policy holder's city.

For example if thhe data is
PID, TIV_2011, TIV_2012, lat, lon


  1. 1, 300, 400.5, 60, 70

  2. 2, 300, 500.7, 70, 80

  3. 3, 400, 400, 60, 90

  4. 4, 500, 600, 80, 80

  5. 5, 400, 300.1, 6, 6



The answer would be 1601.30. Sum of (300.1, 400, 500.7, 400.5)

So, far I have come up with this

SELECT SUM(TIV_2012) FROM Insurance WHERE NOT UNIQUE(SELECT TIV_2011 from Insurance);

This does not work, I am getting an error. Someone pls help.

Answer
SELECT SUM(t1.TIV_2012)
FROM Insurance t1
INNER JOIN
(
    SELECT TIV_2011
    FROM Insurance
    GROUP BY TIV_2011
    HAVING COUNT(*) > 1
) t2
    ON t1.TIV_2011 = t2.TIV_2011
INNER JOIN
(
    SELECT lat, lon
    FROM Insurance
    GROUP BY lat, lon
    HAVING COUNT(*) = 1
) t3
    ON t1.lat = t3.lat AND
       t1.lon = t3.lon