PHPize Online / SQLize Online  /  SQLtest Online

<?php $coll = collect( [ ['player_id' => 1, 'opposition_id' => 10, 'result' => 'won', 'points' => 2], ['player_id' => 1, 'opposition_id' => 11, 'result' => 'lost', 'points' => 0], ['player_id' => 1, 'opposition_id' => 12, 'result' => 'lost', 'points' => 0], ['player_id' => 1, 'opposition_id' => 10, 'result' => 'won', 'points' => 2], ['player_id' => 1, 'opposition_id' => 11, 'result' => 'lost', 'points' => 0], ['player_id' => 1, 'opposition_id' => 10, 'result' => 'lost', 'points' => 0], ['player_id' => 1, 'opposition_id' => 12, 'result' => 'won', 'points' => 2] ] ); var_export( $coll ->groupBy('opposition_id') ->map(fn($group, $oppoId) => [ 'opposition_id' => $oppoId, 'won' => 0, 'lost' => 0, ...$group->pluck('result')->countBy(), 'points' => $group->sum('points'), ]) ->values() ->toArray() );