SQL | SOVESQL 2024 🎄: Day 12. 3년간 들어온 소장품 집계하기

 

Day 12. 3년간 들어온 소장품 집계하기

https://solvesql.com/problems/summary-of-artworks-in-3-years/

WITH T1 AS (
  SELECT YEAR(acquisition_date) AS YEAR
    , classification
  FROM artworks
  WHERE acquisition_date BETWEEN '2014-01-01' AND '2016-12-31'
), T2 AS (
  SELECT classification 
  FROM artworks
  GROUP BY classification
)

SELECT T2.classification
  , COUNT(CASE WHEN YEAR = 2014 THEN T1.classification END) AS '2014'
  , COUNT(CASE WHEN YEAR = 2015 THEN T1.classification END) AS '2015'
  , COUNT(CASE WHEN YEAR = 2016 THEN T1.classification END) AS '2016'
FROM T2
  LEFT JOIN T1 ON T2.classification = T1.classification
GROUP BY T2.classification
ORDER BY T2.classification ASC

❗COUNT(), CASE WHEN 사용해서 년도에 해당하는 경우에 개수 세기
❗LEFT JOIN 으로 생성한 테이블끼리 결합
❗GROUP BY에 classification 넣기

 

 

 

+) WITH 절 : T1

❗WHERE 절에 BETWEEN 사용해서 날짜 필터링

 

 

 

+) WITH 절 : T2

❗classification으로 GROUP BY