How to reorganise the values of a groupBy from the same table (MYSQL)?

Question

I have a Chats table setup like this:

ID	CHAT_GROUP_ID	MESSAGE	READ	CREATED_AT
1	uu-34uu5-6662	hi1	1	2022-06-02 13:16:42
2	uu-34uu5-6662	hi2	1	2022-06-02 13:16:45
3	uu-34uu5-6663	hi3	0	2022-06-02 13:16:46
4	uu-34uu5-6663	hi4	0	2022-06-02 13:16:47
5	uu-34uu5-6664	hi5	0	2022-06-02 13:16:49

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?

Answer 1

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.