I have mySQL table like below, Is there any way I could update the value in table2 to where ? is placed. need the last 5 Days Average Amount when the sales actually registered, from last 5 dates from table-1 for(9th, 7th, 3rd, 30th, 28th)
| | Table1 | | -------- | | Table2 | | |---------|------------|--------|----------|---------|------------|----------| | Brand | Date | Amount | | Brand | As_on | 5day_Avg | | Samsung | 2020-07-09 | 2000 | | Apple | 2020-07-09 | ? | | Nokia | 2020-07-09 | 200 | | Samsung | 2020-07-09 | ? | | Apple | 2020-07-09 | 600 | | Nokia | 2020-07-09 | ? | | Samsung | 2020-07-07 | 450 | | Others | 2020-07-09 | ? | | Nokia | 2020-07-07 | 12 | | | | | | Apple | 2020-07-03 | 450 | | | | | | Samsung | 2020-07-03 | 45 | | | | | | Nokia | 2020-07-03 | 350 | | | | | | Others | 2020-06-30 | 450 | | | | | | Apple | 2020-06-30 | 500 | | | | | | Samsung | 2020-06-30 | 850 | | | | | | Nokia | 2020-06-28 | 100 | | | | | | Others | 2020-06-27 | 50 | | | | | | Samsung | 2020-06-26 | 125 | | | | |
Advertisement
Answer
here is one way:
select brand,avg(amount) last5avg from ( select * , row_number() over (partition by brand order by date desc) rn from table1) t where t.rn <= 5 group by brand
to update :
update table2 inner join ( select brand,avg(amount) last5avg from ( select * , row_number() over (partition by brand order by date desc) rn from table1) t where t.rn <= 5 group by brand) t1 on t1.brand = t2.brand set table2.5day_Avg = t1.last5avg;