0

Lets say I have a database full of users. And a table filled with the "points" users have earned for various activities the Points table has these columns:

id, user_id, value, created_at, updated_at

I want to be able to get a user's total points, easy as pie using the ->sum() method, then compare that user's total points to the average user's total points.

I know that I can get a collection of each users total points via:

->groupBy('user_id')->selectRaw( "sum( value ) as sum" )->pluck( "sum" );

but I'm drawing a blank as to how to get the average total score across all users, instead of an array of each user's total score.

Thanks!

Jeremy Kalgreen
  • 71
  • 7

1 Answers1

3

With laravel you can use the sum() method to sum up the values within a collection and the count() method to count the number elements in the collection. Sum/count is the average:

$sums=$whatever->groupBy('user_id')->selectRaw( "sum( value ) as sum" )->get( "sum" ); 
$avg=$sums->sum("sum")/$sums->count();

Or you need to use a subquery in sql code.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Thanks! For whatever reason I convinced myself the right day to do this should be doing it all in a single mysql query, but that makes total sense. – Jeremy Kalgreen Nov 16 '17 at 05:04
  • You can do it in a mysql query, but do not ask me how that looks like in laravel - I can only do it in raw sql format. However, its logic would be the same: a subquery would calculate the sums per user, the outer query would average the sums into a single query. See the following SO question for the method in sql: https://stackoverflow.com/questions/1572831/avg-of-a-sum-in-one-query – Shadow Nov 16 '17 at 06:05