title: How to join 2 tables with group by query using laravel
body: <p>i have a users and chats table in my database. Here are some sample data in my tables:</p>
  
  <p>users table:</p>
  
  <pre><code>id  |   name  |    avatar
  1       john     default.png
  2       mark       picture.jpg
  </code></pre>
  
  <p>chats table:</p>
  
  <pre><code>   id  |   user_id  |  friend_id | message | status
      1        1           2           hello     0
      2        1           2           hi        1
  </code></pre>
  
  <p>the purpose of the status column is to determine whether the message is already read.</p>
  
  <pre><code>  status 0 = unread message
    status 1 = read message
  </code></pre>
  
  <p>Here is my code to group and count all the unread messages:</p>
  
  <pre><code>        $chat = DB::table('chats')
               -&gt;join('users', 'users.id', '=', 'chats.user_id')
               -&gt;select('user_id', DB::raw('count(*) as total'))
               -&gt;where('friend_id', Auth::user()-&gt;id)
               -&gt;where('status',0)
               -&gt;groupBy('user_id')
               -&gt;get();
  </code></pre>
  
  <p>Here is my code when i tried to insert the users avatar but i get this error: <code>users.avatar' isn't in GROUP BY</code></p>
  
  <pre><code>        $chat = DB::table('chats')
               -&gt;join('users', 'users.id', '=', 'chats.user_id')
               -&gt;select('users.avatar','user_id', DB::raw('count(*) as total'))
               -&gt;where('admin_id', Auth::user()-&gt;id)
               -&gt;where('status',2)
               -&gt;groupBy('user_id')
               -&gt;get();
  </code></pre>
  
  <p>What i want is to also get the avatar of the user who sent the messages. I don not know how to structure my query to get the avatar column. Any help would be appreciated. Thanks.</p>