I’m trying to calculate the average in this sample; This example is working (but only when I select a specific ID, rather than the avg for every ID limited to 20 entries) but I’m having a hard time remembering how to calculate this for every id within the database, rather than the developer specifying the ID explicitly (in this case as 2958). I.E. It would be optimal to have the following rows (assuming this is grouped by each primary key with a limit of 20 values per avg):
-
ID: 1 -> avg 5
-
ID: 2 -> avg 2
-
ID: 3 -> avg 7
-
etc….
select avg(acc.amt) from ( select acc.amt amt from main_acc main_acc join transactions trans on main_acc.id = trans.main_acc_id where main_acc.id = 2958 order by main_acc.track_id, transactions.transaction_time desc limit 20 ) acc;
Any help at all would be greatly appreciated. The only relevant columns are the ones shown above, I can add a schema definition if requested. Thank you!
2
Answers
In fact you do not need the subquery.
How do you define most recent. Is there a timestamp which is not shown, do you use the greatest id, something else? Basically, you order by that criteria and then use limit. So expanding the answer from @Tarik and assuming highest ids as most recent would yield something like: