Sum multiple columns in multiple rows

I have following data structure for sales:

id  year  M01  M02  M03  M04  M05 M06 M07 M08 M09 M10 M11 M12
 1  2020    0    5    4    0   10   0   0   0   0   0   0   0
 1  2019    4    3    0    0    6   0   7   0   8   0   0  10
 2  2020    0    5    4    0   10   0   0   0   0   0   0   0
 2  2019    4    3    0    0    6   0   7   0   8   0   0  10

I need to know how many products with id=1 were sold for the last 12 month. If we are in June 2020 I need to sum M01, M02, M03, M04, M05 (WHERE year=2020) and M06, M07, M08, M09, M10, M11, M12 (WHERE year=2019) WHERE id=1. I should get a value of 36.

Please, any suggestions on how to do that in MySQL?

Answer

You need to fix your data model. Unpivot and then aggregate:

with reasonable_format as (
      select id, year, str_to_date(concat(year, '-01-01'), '%Y-%m-%d') as date, m01 as amount from sales union all
      select id, year, str_to_date(concat(year, '-02-01'), '%Y-%m-%d') as date, m02 from sales union all
      . . .
      select id, year, str_to_date(concat(year, '-02-01'), '%Y-%m-%d') as date, m12 from sales 
    )
select sum(amount)
from reasonable_format rf
where id = 1 and date <= curdate - interval 1 year;

reasonable_format is what your data should look like.