CODEWITHSUNDEEP
mysqlsqlaverage
BrianB
BrianB

Reputation: 143

I need to get the average for every 3 records in one table and update column in separate table

Table Mytable1

Id |  Actual

1  ! 10020

2  | 12203

3  | 12312 

4  | 12453

5  | 13211

6  | 12838

7  | 10l29

Using the following syntax:

 SELECT AVG(Actual), CEIL((@rank:=@rank+1)/3) AS rank FROM mytable1 Group BY rank;

Produces the following type of result:

| AVG(Actual) | rank |

+-------------+------+

|  12835.5455 |    1 |

|  12523.1818 |    2 |

|  12343.3636 |    3 |

I would like to take AVG(Actual) column and UPDATE a second existing table Mytable2

Id | Predict  | 

1  | 11133 

2  | 12312  

3  | 13221

I would like to get the following where the Actual value matches the ID as RANK

Id | Predict  | Actual

1  | 11133    | 12835.5455

2  | 12312    | 12523.1818 

3  | 13221    | 12343.3636

IMPORTANT REQUIREMENT

I need to set an offset much like the following syntax:

SELECT @rank := @rank + 1 AS Id , Mytable2.Actual FROM Mytable LIMIT 3 OFFSET 4);

PLEASE NOTE THE AVERAGE NUMBER ARE MADE UP IN EXAMPLES

Upvotes: 0

Views: 60

Answers (1)

radar
radar

Reputation: 13425

you can join your existing query in the UPDATE statement

UPDATE Table2 T2
JOIN (
   SELECT AVG(Actual) as AverageValue, 
       CEIL((@rank:=@rank+1)/3) AS rank 
   FROM Table1, (select @rank:=0) t 
   Group BY rank )T1
on T2.id = T1.rank
SET Actual = T1.AverageValue

Upvotes: 1

Related Questions