# 585. Investments in 2016
[leetcode](https://leetcode.com/problems/investments-in-2016/description/)
他要我們找到2016年的帳單的同時
2015的資料必須與經緯度一致
所以我們必須要用子查詢
由於他要求我們必須要查到小數點第二位
我們先使用

SELECT ROUND(SUM(tiv_2016), 2) AS tiv_2016

然後就是分別的查詢
因為2015的單子我們需要找到重複的 因為我們必須將收益相加
所以先找出所有2015的單子數據是一樣的

WHERE tiv_2015 IN (
    SELECT tiv_2015
    FROM Insurance
    GROUP BY tiv_2015
    HAVING COUNT(*) > 1
)

再來經緯度必須要不同 所以只能一個 不能重複
然後AND 代表我們必須同時滿足

AND (lat, lon) IN (
    SELECT lat, lon
    FROM Insurance
    GROUP BY lat, lon
    HAVING COUNT(*) = 1

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
)