Sum with SQL depending on the value of a column

Question

I have 3 columns : year, price, and day_type.

year day_type price
2016 0 10
2016 1 20
2016 2 5
2017 0 14
2017 1 6
2017 2 3

I want to keep only the lines where day_type = 1 or 2, but add to these lines the value when day_type = 0.

Expected Result :

year day_type price
2016 1 30
2016 2 15
2017 1 20
2017 2 17

How can I do that?

Answer 1

You can use a join:

select t.year, t.day_type, (t.price + coalesce(t0.price, 0)) as price
from t left join
     t t0
     on t.year = t0.year and t0.day_type = 0
where t.day_type <> 0;

This uses left join in case one of the years does not have a 0 price.