title: Sql Count is returning all row values
body: <p>I have two table users and messages. Messages have status type 1 = unread . I want to get users data and latest message and message time. and also count of messages where messages.status = 1</p>
  <pre><code> SELECT users.id, users.name,users.gender,users.status,users.image,users.device_id,users.created_at,users.updated_at,
    MAX(messages.created_at) as message_at,
      messages.user_id, messages.body as message,
      (SELECT COUNT(messages.id) WHERE messages.status = 1 and messages.user_id = users.id) as unread
       from messages 
       JOIN users on users.id = messages.user_id  OR users.id = messages.to_id
       GROUP BY user_id 
       ORDER BY message_at DESC   
  </code></pre>
  <p>Above queries works. But count of unread it returns wrong numbers.</p>
  <p><strong>Edits</strong></p>
  <ul>
  <li>I changed This query many problems solves like duplication. But still message count is wrong. I don't know. When All the messages.status != 1 its returns 0 as unread. But When there is one and more messages.status = 1 its shows wrong number :(</li>
  </ul>
  <p>Here is updated query.</p>
  <pre><code>     SELECT  users.*,messages.body as message,messages.created_at as message_at,messages.type as message_type,
      (SELECT  COUNT(messages.id) WHERE (messages.status = 1 and users.id = messages.user_id) ) as unread
       from users 
       JOIN (
       SELECT  messages.*
              FROM messages
               ORDER BY messages.created_at DESC
       ) 
       messages on users.id = messages.user_id  OR users.id = messages.to_id
        GROUP BY users.id 
        ORDER BY message_at DESC  
  </code></pre>
  <p><strong>Edits 2.</strong></p>
  <p>I have two table.
  1 - users</p>
  <p><a href="https://i.stack.imgur.com/RafTE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/RafTE.png" alt="enter image description here" /></a></p>
  <p>2 - messages [user_id = sender id &amp; to_id = receiver id]</p>
  <p><a href="https://i.stack.imgur.com/3aRgn.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3aRgn.png" alt="enter image description here" /></a></p>
  <p>Desired Result.
  I want to query all users. with latest message from messages ( for this I have to query all messages with user_id=id or to_id=id). Also with count on unread ( for this messages.status=1) messages.</p>