Using Join with Group By in Laravel for Count

we have following tables

Subjects Table Students Table

We want output like following

Expected Output

Here is what we have done till now, I mean the query

$enrolledStudents = Student::select('students.subject_id', 'subjects.subject_name as subject_name', DB::raw('count(subject_id) as total'))
        ->leftJoin('subjects', 'subjects.id', '=', 'students.subject_id')
        ->groupBy('students.subject_id')
        ->get();

This query is not working,

Syntax error or access violation: 1055 'subjects.subject_name' isn't in GROUP BY

what can we do the get the expected output

Answer

Select only columns that are aggregated or present in group by clause, like subject_name is not aggregated nor part of group by clause so in strict mode databases will throw this error as this operation is not allowed.

Moreover you should use Subject model as left table for join so that you will get all the subjects even if there are no enrolled students in it

Subject::from('subjects as s')
        ->select('s.subject_name', DB::raw('count(s.id) as total'))
        ->leftJoin('students as st', 's.id', '=', 'st.subject_id')
        ->groupBy('s.subject_name')
        ->get();

Or if you have defined the relations between your Subject and Student model then you can use withCount()

class Subject extends Model
{
    public function students()
    {
        return $this->hasMany(Student::class, 'subject_id', 'id');
    }
}

Subject::withCount('students')->get();