rolling averages for a part of the table
Hi all !
i have the following table in my DB
The averages are the rolling averages for 5 records at a time. If I try and get the same for only the user mina1111 using the query
SELECT user.RecNo as RecNo, user.User as User, user.V_Score as V_Score, avg(user_past.V_Score) as Average
FROM user
JOIN user as user_past on user_past.RecNo between user.RecNo-4 AND user.RecNo WHERE user.User = 'mina1111'
GROUP by RecNo, V_Score;
I get the following :-
The averages go all
Sir I understand that but I am unable to get those averages from the table that I created. As mentioned in #3, even though I get the 5 rows, I keep getting the Can't reopen error when I try and use it for getting the averages.
For your convenience here it is again.
create temporary table mina1111_a
Select * from (
select a.RecNo
, a.User
, a.V_Score
from ajoo as a
where User = 'mina1111'
ORDER BY RecNo DESC LIMIT 5)sub
Order by RecNo ASC;
which gives the table
+-------