# leetcode\_585

SQL架构 Write a query to print the sum of all total investment values in 2016 (TIV\_2016), to a scale of 2 decimal places, for all policy holders who meet the following criteria:

Have the same TIV\_2015 value as one or more other policyholders. Are not located in the same city as any other policyholder (i.e.: the (latitude, longitude) attribute pairs must be unique). Input Format: The insurance table is described as follows:

| Column Name | Type          |
| ----------- | ------------- |
| PID         | INTEGER(11)   |
| TIV\_2015   | NUMERIC(15,2) |
| TIV\_2016   | NUMERIC(15,2) |
| LAT         | NUMERIC(5,2)  |
| LON         | NUMERIC(5,2)  |

where PID is the policyholder's policy ID, TIV\_2015 is the total investment value in 2015, TIV\_2016 is the total investment value in 2016, LAT is the latitude of the policy holder's city, and LON is the longitude of the policy holder's city.

Sample Input

| 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  |

Sample Output

| TIV\_2016 |
| --------- |
| 45.00     |

Explanation

The first record in the table, like the last record, meets both of the two criteria. The TIV\_2015 value '10' is as the same as the third and forth record, and its location unique.

The second record does not meet any of the two criteria. Its TIV\_2015 is not like any other policyholders.

And its location is the same with the third record, which makes the third record fail, too.

So, the result is the sum of TIV\_2016 of the first and last record, which is 45.

## Solutions

1. **subquery with JOIN**

```sql
# Write your MySQL query statement below
SELECT ROUND(SUM(TIV_2016), 2) AS TIV_2016
    FROM insurance
    WHERE PID NOT IN (SELECT DISTINCT(i1.PID) 
                    FROM insurance i1, insurance i2 
                    WHERE i1.LAT = i2.LAT AND i1.LON = i2.LON AND i1.PID != i2.PID) 
      AND TIV_2015 IN (SELECT TIV_2015
                        FROM insurance
                        GROUP BY TIV_2015
                        HAVING COUNT(*) > 1)
```

or

```sql
# Write your MySQL query statement below
SELECT ROUND(SUM(TIV_2016), 2) AS TIV_2016
    FROM insurance
    WHERE PID IN (SELECT PID 
                    FROM insurance
                    GROUP BY LAT, LON
                    HAVING COUNT(*) = 1) 
    AND TIV_2015 IN (SELECT TIV_2015
                    FROM insurance
                    GROUP BY TIV_2015
                    HAVING COUNT(*) > 1)
```