[Leet 585] Investments in 2016

lat, lon 가 unique한 행들 중

tiv_2015 값이 동일한 행들이 2개 이상있는 row들만 추출해서

해당 row들의 tiv_2016의 합계를 구하는 문제이다.

 

GROUP BY와 HAVING절 COUNT를 통해서 unique하고 unique하지 않은 행들을 추출해내는게 핵심이다

 

---

https://leetcode.com/problems/investments-in-2016/description/

 

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
)