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

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

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

The College Table

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

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

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

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

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