PenAndPapers
PenAndPapers

Reputation: 2108

Laravel how to get the average of two result values

So I have a query that return all the stats of a player from their last season and current season. What I want to do is get the average of a player from last season and current season and make in to a one object only. Below is my query and sample result.

Query

public static function getAllTimeLeaders($request) {
        $competitionIds = self::getLasTwoSeason();
        $data = DB::table('leader_board_stats as lbs')
            ->select([
                'sGamesStarted',
                'sMinutesAverage',
                'sPointsAverage',
                'sFieldGoalsMadeAverage',
                'sFieldGoalsAttemptedAverage',
                'sFieldGoalsPercentage',
                'sTwoPointersMadeAverage',
                'sTwoPointersAttemptedAverage',
                'sThreePointersMadeAverage',
                'sThreePointersAttemptedAverage',
                'sThreePointersPercentage',
                'sFreeThrowsMadeAverage',
                'sFreeThrowsAttemptedAverage',
                'sReboundsTotalAverage',
                'sAssistsAverage',
                'sStealsAverage',
                'sBlocksAverage',
                'sTurnoversAverage',
                'sFoulsPersonalAverage',
                'firstName',
                'familyName',
                'personId',
                'teamId',
                'competitionId'
            ])
            ->where('lbs.periodNumber', '0')
            ->whereIn('lbs.competitionId', [$competitionIds[0]->competitionId, $competitionIds[1]->competitionId])
            ->get();
        echo '<pre>';
        var_dump($data);
    }

Sample of returned value

[
    [0]=> object(stdClass)#262 (24) {
      ["sGamesStarted"]=>
      int(6)
      ["sMinutesAverage"]=>
      float(17.66)
      ["sPointsAverage"]=>
      float(7.71)
      ["sFieldGoalsMadeAverage"]=>
      float(2.59)
      ["sFieldGoalsAttemptedAverage"]=>
      float(5.62)
      ["sFieldGoalsPercentage"]=>
      float(0.46)
      ["sTwoPointersMadeAverage"]=>
      float(1.47)
      ["sTwoPointersAttemptedAverage"]=>
      float(2.53)
      ["sThreePointersMadeAverage"]=>
      float(1.12)
      ["sThreePointersAttemptedAverage"]=>
      float(3.09)
      ["sThreePointersPercentage"]=>
      float(0.36)
      ["sFreeThrowsMadeAverage"]=>
      float(1.41)
      ["sFreeThrowsAttemptedAverage"]=>
      float(1.76)
      ["sReboundsTotalAverage"]=>
      float(2.85)
      ["sAssistsAverage"]=>
      float(1.71)
      ["sStealsAverage"]=>
      float(0.68)
      ["sBlocksAverage"]=>
      float(0.21)
      ["sTurnoversAverage"]=>
      float(0.91)
      ["sFoulsPersonalAverage"]=>
      float(2.35)
      ["firstName"]=>
      string(3) "Person1"
      ["familyName"]=>
      string(8) "Familyname"
      ["personId"]=>
      int(28831)
      ["teamId"]=>
      int(3749)
      ["competitionId"]=>
      int(9224)
    },
    [88]=> object(stdClass)#527 (24) {
      ["sGamesStarted"]=>
      int(11)
      ["sMinutesAverage"]=>
      float(23.28)
      ["sPointsAverage"]=>
      float(9.5)
      ["sFieldGoalsMadeAverage"]=>
      float(3.07)
      ["sFieldGoalsAttemptedAverage"]=>
      float(7.97)
      ["sFieldGoalsPercentage"]=>
      float(0.38)
      ["sTwoPointersMadeAverage"]=>
      float(1.4)
      ["sTwoPointersAttemptedAverage"]=>
      float(3.47)
      ["sThreePointersMadeAverage"]=>
      float(1.67)
      ["sThreePointersAttemptedAverage"]=>
      float(4.5)
      ["sThreePointersPercentage"]=>
      float(0.37)
      ["sFreeThrowsMadeAverage"]=>
      float(1.7)
      ["sFreeThrowsAttemptedAverage"]=>
      float(2.2)
      ["sReboundsTotalAverage"]=>
      float(3.57)
      ["sAssistsAverage"]=>
      float(1.43)
      ["sStealsAverage"]=>
      float(0.93)
      ["sBlocksAverage"]=>
      float(0.23)
      ["sTurnoversAverage"]=>
      float(0.97)
      ["sFoulsPersonalAverage"]=>
      float(2.77)
      ["firstName"]=>
      string(3) "Person1"
      ["familyName"]=>
      string(8) "Familyname"
      ["personId"]=>
      int(28831)
      ["teamId"]=>
      int(3749)
      ["competitionId"]=>
      int(2254)
    }
]

Upvotes: 0

Views: 814

Answers (2)

Luis Lopez
Luis Lopez

Reputation: 1277

Try: avg() is easier and cleaner.

Docs: https://laravel.com/docs/5.4/collections#method-avg

Upvotes: 1

PenAndPapers
PenAndPapers

Reputation: 2108

Finally I figured it out I just need to use DB::raw and AVG of each stats fields then group it by personId.

public static function getAllTimeLeaders($request) {
        $competitionIds = self::getLasTwoSeason();
        $data = DB::table('leader_board_stats as lbs')
            ->select([
                DB::raw('AVG(sGamesStarted)'),
                DB::raw('AVG(sMinutesAverage)'),
                DB::raw('AVG(sPointsAverage)'),
                DB::raw('AVG(sFieldGoalsMadeAverage)'),
                DB::raw('AVG(sFieldGoalsAttemptedAverage)'),
                DB::raw('AVG(sFieldGoalsPercentage)'),
                DB::raw('AVG(sTwoPointersMadeAverage)'),
                DB::raw('AVG(sTwoPointersAttemptedAverage)'),
                DB::raw('AVG(sThreePointersMadeAverage)'),
                DB::raw('AVG(sThreePointersAttemptedAverage)'),
                DB::raw('AVG(sThreePointersPercentage)'),
                DB::raw('AVG(sFreeThrowsMadeAverage)'),
                DB::raw('AVG(sFreeThrowsAttemptedAverage)'),
                DB::raw('AVG(sReboundsTotalAverage)'),
                DB::raw('AVG(sAssistsAverage)'),
                DB::raw('AVG(sStealsAverage)'),
                DB::raw('AVG(sBlocksAverage)'),
                DB::raw('AVG(sTurnoversAverage)'),
                DB::raw('AVG(sFoulsPersonalAverage)'),
                'firstName',
                'familyName',
                'personId',
                'teamId',
                'competitionId'
            ])
            ->where('lbs.periodNumber', '0')
            ->whereIn('lbs.competitionId', [$competitionIds[0]->competitionId, $competitionIds[1]->competitionId])
            ->groupBy('lbs.personId')
            ->get(); 

       dd($data);
    }

Upvotes: 0

Related Questions