skip to Main Content

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


  1. select main_acc.id, avg(acc.amt) from (select acc.amt amt
        from main_acc main_acc
                 join transactions trans on main_acc.id = trans.main_acc_id
        
        order by main_acc.track_id, transactions.transaction_time desc) acc
    group by main_acc.id;
    

    In fact you do not need the subquery.

    select acc.id, avg(acc.amt)
        from main_acc acc
                 join transactions trans on acc.id = trans.main_acc_id
    group by acc.id
    
    Login or Signup to reply.
  2. 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:

    select acc.id, avg(acc.amt) avg_for_id
        from main_acc acc
                 join transactions trans on acc.id = trans.main_acc_id
    group by acc.id
    order by acc.id desc
    limit 20;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search