title: Count based on condition using query builder not working
body: <p>I'm experiencing some issues with the result set I'm getting from my Laravel query</p>
  <p><strong>The Query</strong></p>
  <pre><code>     $collegecounts = DB::table('colleges as cs')
          -&gt;select(DB::raw('count(college_id) as graduates'),'cs.id','cs.collegename',
              DB::raw('round(AVG(st.status_id),0) as average'),
              DB::raw(&quot;(CASE WHEN (st.status_id = 4) THEN count(st.college_id) END) as placements&quot;)
  
          )
          -&gt;join('students as st', 'st.college_id', '=', 'cs.id')
          -&gt;groupBy('st.college_id', 'cs.id', 'cs.collegename')
          -&gt;get();
  </code></pre>
  <p>Expected results are similar to the table below: (Placements column on the table below is a status_id count)</p>
  <p><a href="https://i.stack.imgur.com/uaf9V.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/uaf9V.png" alt="enter image description here" /></a></p>
  <p>SQL Table:</p>
  <p><strong>The Student Table</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Column</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>id</td>
  </tr>
  <tr>
  <td>Name</td>
  </tr>
  <tr>
  <td>College_id</td>
  </tr>
  <tr>
  <td>Status_id</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><strong>The College Table</strong></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Column</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>id</td>
  </tr>
  <tr>
  <td>Name</td>
  </tr>
  <tr>
  <td>location</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>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 <code>having</code> instead of the <code>case condition</code> I get the same result</p>
  <p>The error:</p>
  <blockquote>
  <p>SQLSTATE[42803]: Grouping error: 7 ERROR: column &quot;st.status_id&quot; 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_...
  ^</p>
  </blockquote>
  <p>I can easily add st.status_id to the <code>group by</code>, however that doesn't get me the correct result</p>