具有连接表的多列SUM - Laravel 5.2

Question

I have a two tables winners and check_winners.

winners:

id     check_winner_id      username     win_number ....
 1                   1           xxx              1
 2                   2           xxx              1
 3                   3           yyy              1
 4                   4           yyy              1

check_winners:

id       user_id      chance_of_win....
 1             1                  1
 2             1                  1
 3             2                  1
 4             2                  1
 5             2                  1
 6             2                  1
 7             2                  1
 8             2                  1

Now i want to join two tables and want to sum chance_of_win column and win_number column group by username. I have tried an way but its not give exactly result for sum of win_number. How can i achieve this?

$winners = DB::table('winners')
            ->groupBy('winners.username')
            ->leftJoin('check_winners', 'winners.id', '=', 'check_winners.user_id')
            ->selectRaw('*, sum(check_winners.chance_of_win) as chance_sum, sum(winners.win_number) as win_sum')         
dd($winners);

with this i get output:

array:2 [▼
  0 => {#241 ▼
    +"id": 1
    +"check_winner_id": 1
    +"username": "xxx"
    +"chance_sum": "2"
    +"win_sum": "2"
  }
  1 => {#242 ▼
    +"id": 2
    +"check_winner_id": 2
    +"username": "yyy"
    +"chance_sum": "6"
    +"win_sum": "6"
  }
]

but win_sum should be 2 for both array. Where i did wrong? Thanks in advance.

Answer 1

You should try this:

$winners = DB::table('winners')
            ->leftJoin('check_winners', 'winners.id', '=', 'check_winners.user_id')
            ->selectRaw('*, sum(check_winners.chance_of_win) as chance_sum, sum(winners.win_number) as win_sum')
            ->groupBy('winners.username')       
dd($winners);

Updated answer

$winners = DB::table('winners')
            ->select('*', DB::raw("SUM(check_winners.chance_of_win) as chance_sum"), DB::raw("SUM(winners.win_number) as win_sum"))
            ->leftJoin('check_winners', 'winners.id', '=', 'check_winners.user_id')
            ->groupBy('winners.username')