I'm experiencing some issues with the result set I'm getting from my Laravel query
The Query
$collegecounts = DB::table('colleges as cs') ->select(DB::raw('count(college_id) as graduates'),'cs.id','cs.collegename', DB::raw('round(AVG(st.status_id),0) as average'), DB::raw("(CASE WHEN (st.status_id = 4) THEN count(st.college_id) END) as placements") ) ->join('students as st', 'st.college_id', '=', 'cs.id') ->groupBy('st.college_id', 'cs.id', 'cs.collegename') ->get(); Expected results are similar to the table below: (Placements column on the table below is a status_id count)
SQL Table:
The Student Table
| Column |
|---|
| id |
| Name |
| College_id |
| Status_id |
The College Table
| Column |
|---|
| id |
| Name |
| location |
The status is found in the status table. If I don't add the status_id in the groupby query, I get an error that requires me to add status_id to groupby. Upon adding it to Groupby, then I get more columns, for each student, instead of the grouping by college. I also tried using having instead of the case condition I get the same result
The error:
SQLSTATE[42803]: Grouping error: 7 ERROR: column "st.status_id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: ...ound(AVG(st.status_id),0) as average, (CASE WHEN (st.status_... ^
I can easily add st.status_id to the group by, however that doesn't get me the correct result
