dimanche 17 juillet 2016

Get averages across multiple columns with GroupBy

I want to compile statistics on how users are answering questions for my app. I am using Laravel 5.1 for my API which will handle this.

I have x entries in my questions_user table, and want to first group by question_id, then get the averages per each group for user_timing, and other potential fields.

enter image description here

    $user_questions = DB::table('question_user')
        ->select('question_id', 'user_timing', DB::raw('count(*) as total'))
        ->groupBy('question_id')
        ->get();

Gets me:

[
 {
  "question_id": 1,
  "user_timing": "5",
  "total": 2
 },
 {
  "question_id": 2,
  "user_timing": "10",
  "total": 1
 },
 {
  "question_id": 3,
  "user_timing": "4",
  "total": 3
 }
]

If I add ->avg('user_timing') just before the get(), it gives error:

"Call to a member function get() on double"

I'm assuming this is because avg computes the value and returns it to the get(), which is expecting a query object.

if I add ->avg('user_timing') in place of get(), it returns a single value 4, which is not the average of anything, let alone groups.

How can I return aggregate values across multiple fields in multiple groups?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire