CODEWITHSUNDEEP
mysqlsql
Avi
Avi

Reputation: 63

mySQL update average amount of last 5 dates when there is gap in dates

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    |          |         |            |          |

Upvotes: 0

Views: 23

Answers (1)

eshirvana
eshirvana

Reputation: 24568

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;

Upvotes: 1

Related Questions