Sum multiple columns in multiple rows

Question

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?

I'd strongly recommend against denormalized table like that. I'd suggest normalizing it with Month and Value fields. This is only the beginning of many to come complexities. The table above should only be a view. — Nae
– Nae, Commented Jun 2, 2020 at 10:56
Unfortunately, I have to deal with this structure... Many thanks to Gordon Linoff! You saved me. The only correction: date >= curdate - interval 1 year. Well, I had to introduce several more modifications beause of old MySQL version, but, in principle, that's what I wanted. — user3193922
– user3193922, Commented Jun 3, 2020 at 9:08

Gordon Linoff · Accepted Answer · 2020-06-02 10:59:24Z

1

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.

answered Jun 2, 2020 at 10:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sum multiple columns in multiple rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related