Count based on condition using query builder not working

Question

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

Felix · Accepted Answer · 2020-12-27 15:31:40Z

1

You can use 'sum' for counting with condition. The query below as your expected result:

select c.name, count(s.id) as Graduates, SUM(s.status_id=4) AS Placements
from college as c join student as s on s.collegge_id = c.id
group by c.id

answered Dec 27, 2020 at 15:31

Felix

5811 gold badge14 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Count based on condition using query builder not working

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related