Sql Count is returning all row values

Question

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

 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

Above queries works. But count of unread it returns wrong numbers.

Edits

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 :(

Here is updated query.

     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

Edits 2.

I have two table. 1 - users

2 - messages [user_id = sender id & to_id = receiver id]

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.

user_16267722 · Answer

I think It's because of OR operator .In this query you need exactly " JOIN on users.id = messages.user_id", but if You use OR operator this condition will has wrong numbers. I don't find out column "message.to_id". It works without OR:

 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  
 GROUP BY user_id 
 ORDER BY message_at DESC

user_10498828 · Answer

Use window functions like MAX(), FIRST_VALUE() and SUM():

SELECT DISTINCT u.*,
       MAX(m.created_at) OVER (PARTITION BY u.id) AS message_at,
       FIRST_VALUE(m.body) OVER (PARTITION BY u.id ORDER BY m.created_at DESC) AS message,
       SUM(m.status IS 1) OVER (PARTITION BY u.id) AS unread
FROM users u LEFT JOIN messages m
ON u.id IN (m.user_id, m.to_id)

This returns the number of messages with status = 1 of each user as a sender or receiver.

If you want only the number of messages that the user sent:

SUM(m.status IS 1 AND m.user_id IS u.id ) OVER (PARTITION BY u.id) AS unread

or the number of messages that the user received:

SUM(m.status IS 1 AND m.to_id IS u.id ) OVER (PARTITION BY u.id) AS unread

Sql Count is returning all row values

2 Answers