[SQL문제풀기] 3년간 들어온 소장품 집계하기

문제

SQLite

내가 작성한 오답

with a as (
    select
      classification,
      strftime ('%Y', acquisition_date) year,
      count(artwork_id) num
    from artworks
    where strftime ('%Y', acquisition_date) in ('2014', '2015', '2016')
    group by classification, strftime ('%Y', acquisition_date)
  ), 
  b as (select classification, 
        case year when '2014' then num else null end b2014,
        case year when '2015' then num else null end b2015,
        case year when '2016' then num else null end b2016
      from a)
select classification, ifnull(sum(b2014),0) '2014', 
      ifnull(sum(b2015),0) '2015', ifnull(sum(b2016),0) '2016'
from b
group by classification

내가 수정한 정답

distinct 적용하지 않은 결과

distinct 적용한 결과

with a as (select classification, strftime ('%Y', acquisition_date) year, count(artwork_id) num
          from artworks
          where strftime ('%Y', acquisition_date) in ('2014', '2015', '2016')
          group by classification, strftime ('%Y', acquisition_date)), 
      b as (select classification, 
            case year when '2014' then num else null end b2014,
            case year when '2015' then num else null end b2015,
            case year when '2016' then num else null end b2016
          from a)
select a.classification, ifnull(sum(b.b2014),0) '2014', 
      ifnull(sum(b.b2015),0) '2015', ifnull(sum(b.b2016),0) '2016'
from b b
right join (select distinct classification from artworks) a on b.classification= a.classification
group by a.classification