I have a Chats table setup like this:

ID = int
CHAT_GROUP_ID = Varchat(some kind of UUID)
MESSAGE = String

What I am trying to achieve is:

• GROUP ALL THE CHAT_GROUP_ID with their own respective IDs.
• When all the CHAT_GROUP_ID are grouped, SUM all the READ AS SUM(IF(read = 0, 1, 0))
• Finally (and where I am struggling), always show only 1 MESSAGE but always the latest one.

I have am struggling so much on this unbelievable! How can I do this?

CodePudding user response：

Assuming at least MySql 8:

SELECT  ct.Chat_Group_ID, ct.Message, 
    ( SELECT SUM(Case when read = 1 then 0 else 1 end) 
      FROM Chats ct2
      WHERE ct2.Chat_Group_ID = ct.Chat_Group_ID
    ) as Unread
FROM (
    SELECT Chat_Group_ID, Message
       , row_number() over (partition by Chat_Group_ID order by create_at desc) rn
    FROM Chats
) ct 
WHERE ct.rn = 1

CodePudding user response：

If you need it to be done in MySQL v5.*, you can use the following query:

SELECT tab.ID,
       tab.CHAT_GROUP_ID,
       tab.MESSAGE,
       aggregated.SUM_READ_,
       aggregated.MAX_TIME
FROM       tab 
INNER JOIN (SELECT CHAT_GROUP_ID,
                   SUM(IF(READ_=0,1,0)) AS SUM_READ_,
                   MAX(CREATED_AT)      AS MAX_TIME
            FROM tab
            GROUP BY CHAT_GROUP_ID             ) aggregated
        ON tab.CHAT_GROUP_ID = aggregated.CHAT_GROUP_ID
       AND tab.CREATED_AT = aggregated.MAX_TIME

First you create a view containing aggregated value of READ and CREATED_AT with respect to CHAT_GROUP_ID, then use these information to retrieve the needed infos from the main table.

Try it here.