0

I have a database table, which includes points against the player. A Single player have multiple records in the table. I need to group the records by the player_id. And I am trying to get the average of first 3 records of the each group. I tried to reach this over the last evening. But I had not any luck to solve this.

This is my initial code, And I need to modify this to get the AVG of first 3 records of each group(grouped by player_id)

$oSelect = DB::table('statistics')
          ->where('statistics.league_id', $league_id)
          ->select('id, AVG('statistics.points as points'),player_id, scheduled')
          ->orderBy('statistics.scheduled','DESC')
          ->groupBy('statistics.player_id');

Thank you in advanced

Eranga Kapukotuwa
  • 4,542
  • 5
  • 25
  • 30

1 Answers1

0

Try this:

$oSelect = DB::table('statistics')
      ->select(DB::raw('id, AVG(statistics.points) as points,player_id,scheduled'))
      ->where('statistics.league_id', $league_id)
      ->orderBy('statistics.scheduled','DESC')
      ->groupBy('statistics.player_id')->limit(3)->get();

Notice: if you get nonaggregated column error that case you must add id column to grouped by columns list or disable the only_full_group_by option in mySql

Disable ONLY_FULL_GROUP_BY

Tohid Dadashnezhad
  • 1,808
  • 1
  • 17
  • 27
  • Looks, you have misunderstand my question. What I want is not limit the whole data set, but to limit each group. ( get first 3 records of each player and get the AVG ) – Eranga Kapukotuwa Jul 07 '17 at 04:17
  • I'm sorry about that, but your sample code was incorrect and also was too simple according to what you want. But I can say using Laravel DB facade to solve this complicated issue may mess your code and is not logical. so I recommend you to use stored procedure and simply call the function from your code side. if you want to know how to limit records for each group , checkout this page: – Tohid Dadashnezhad Jul 07 '17 at 10:05
  • https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results – Tohid Dadashnezhad Jul 07 '17 at 10:05