How to combine rows(SUM after join) in a table with some condition Laravel

I'm having multiple tables and I combined them using 'join' (code given below), Now I'm having a combined table which gives me correct number of answers for a particular user and for a particular quiz (given below):

enter image description here

I want to add or want to show combined result for same user and quiz like this (given below):

enter image description here

here is my blade code:

   

<thead>

<tr>

<th>Quiz Name</th>

<th>Quiz ID</th>

<th>UserName</th>

<th>User ID</th>

<th>Correct</th>

</tr>

</thead>

<tbody>

@foreach($data_correct as $row)

<tr>

<td>{{$row->qname}}</td>

<td>{{$row->quiz_id}}</td>

<td>{{$row->name}}</td>

<td>{{$row->user_id}}</td>

<td>{{$row->is_correct}}</td>

</tr>

@endforeach

</tbody>

here is my controller function:

   

public function leaderboard(){

        $quizzes = (new Quiz)->allQuiz();

        $users = (new User)->allUsers();

        $data_correct = DB::table('results')

        ->join('answers', 'answers.id', 'results.answer_id')

        ->join('quizzes', 'quizzes.id', 'results.quiz_id')

        ->join('users', 'users.id', 'results.user_id')

        ->select('quizzes.qname', 'results.quiz_id','users.name' ,'results.user_id', 'answers.is_correct')->where('is_correct', 1)

        ->get();

        $data_wrong = DB::table('results')

        ->join('answers', 'answers.id', 'results.answer_id')

        ->select('results.quiz_id', 'results.user_id', 'answers.is_correct')->where('is_correct', 0)

        ->get();

        return view('leaderboard', compact('quizzes', 'data_correct', 'data_wrong'));

     }

quizzes table: 

enter image description here

answers table:

enter image description here

results table:

enter image description here

Answers 0

Related Articles