585. Investments in 2016

문제

Write a solution to report the sum of all total investment values in 2016 tiv_2016, for all policyholders who:

have the same tiv_2015 value as one or more other policyholders, and
are not located in the same city as any other policyholder (i.e., the (lat, lon) attribute pairs must be unique).
Round tiv_2016 to two decimal places. 

 

" tiv_2015의 값이 다른 policyholder와 하나 혹은 그 이상의 값이 같거나, 고유한 (lat, loan) 값을 가져야 한다." 

 

 

문제 풀이 

1. tiv_2015와 중복된 값을 가지는 지 확인하기 위해 서브 쿼리를 이용한다. 

SELECT tiv_2015
FROM Insurance 
GROUP BY tiv_2015 
HAVING count(*) > 1

 

 

2. (lat, loan) 이 두쌍이 고유한 값을 가지는 지 (중복된 것이 없는지) 확인하기 위해 서브쿼리를 이용한다. 이때 GROUP BY 를 한 후에, 그 갯수가 1이면, 중복이 아닌 고유한 값을 가진다. 

 

SELECT lat,lon
FROM Insurance 
GROUP BY lat,lon
HAVING count(*)<=1

 

 

3. 1, 2 번을 WHERE 절에서의 서브쿼리로 만든 후, 평균 값을 구해주면 된다. 

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)