SQL case when combined with group by

lets say I have a table with 2 columns: car_id, and selling_code

• if a car got sold at 2020, no matter how many times, it will have a single line in the table with the code 1.

• if a car got sold at 2019, no matter how many times, it will have a single line in the table with the code 2.

• if it got sold both at 2019 and 2020, it will have 2 lines, one with code 1 and one with code 2.

I want to create from this table a new table, in which there is only a single line for each id, and 2 columns: car_id and selling_text. if the car got sold only at 2020, the selling_text is ‘2020’ else if the car got sold only at 2019, the selling_text is ‘2019’ and if it got sold both at 2020 and 2019, the selling_text is ‘both’

Answer

select car_id,
       case when count(distinct selling_code) = 2 then 'both'
            when max(selling_code) = 1 then '2020'
            else '2019'
       end as selling_text
from your_table
group by car_id