Skip to content

Mysql count with GROUP_CONCAT while using join query

I have a chats table and chat_reactions table. Each chat message can have many reactions and a reaction is a text and it can be many. I am trying to return messages with the grouped reactions and the total number of times a particular reaction is used. For example,

msg: hi with id 1 got a total of three reactions. 1 LIKE AND 2 LOVES. How can I return it?

Here is the query I am trying

SELECT, c.msg, GROUP_CONCAT(cr.reaction) as reaction
FROM chats as c 
LEFT JOIN chat_reactions as cr on = cr.chat_id 

My result looks like this.

[![enter image description here][1]][1]

How can I add numbers with reaction or there are better and performant options I have? Please suggest.

Thank you



First count the post reactions. Then aggregate per post.

  group_concat(concat(cr.reaction, '(', cnt, ')') 
               order by cr.reaction
               separator ', ') as reactions
from chats as c 
left join 
  select chat_id, reaction, count(*) as cnt
  from chat_reactions 
  group by chat_id, reaction
) cr on cr.chat_id =
group by, c.msg
order by;
4 People found this is helpful