Laravel groupBy rank resulting in multiple grouped rows

Question

I'm using the groupby() function on my query to get some stats on two groups of athletes.

I have a table with my athletes and one with their performance.

- athletes table
-- id
-- name
-- group_id

-performance table
-- id
-- DateTime
-- athlete_id
-- sport_id
-- time

Each athlete do the same sport for multiples days (which is not the same for all athletes). I would like to see the evolution of the time average over days per group in a given sport.

I run this query

$query = '*, AVG(time) as average_time' ;
$Performance = Performance::join('athletes','performance.athlete_id','=','athletes.id')
                            ->select(\DB::raw($query))
                            ->where('performance.sport_id', '=', '43')
                            ->orderBy('group_id', 'asc')
                            ->groupBy('group_id')
                            ->get()
                            ->toArray();

However, this give me the average for a given sport_id but not over each days.

How can I see the average for every day in a given sport groupby group_id?

score 0 · Answer 1 · answered Apr 14 '14 at 01:57

If DateTime is a timestamp then you can use the mysql date functions. Specifically GROUP BY WEEKDAY(timestamp_field).

Added into your query it looks like this

$query = '*, AVG(time) as average_time' ;
$Performance = Performance::join('athletes','performance.athlete_id','=','athletes.id')
                            ->select(\DB::raw($query))
                            ->where('performance.sport_id', '=', '43')
                            ->orderBy('group_id', 'asc')
                            ->groupBy('WEEKDAY(DateTime)')
                            ->groupBy('group_id')
                            ->get()
                            ->toArray();

Laravel groupBy rank resulting in multiple grouped rows

1 Answers1