Mysql group by multiple combinations

Question

I'm building a chat app in which two people will comunicate on enquiry, I want to fetch conversation between two users on enquiry, table structure is like below:

Table: enquiries

id, name, product, msg

Table: replies

id, enquiry_id(fk), from_user(fk), to_user(fk), msg
 +----+--------+-----------+---------+------+
 | id | enq_id | from_user | to_user | msg |
 +----+--------+-----------+---------+------+
 | 1 | 1 | 1 | 2 | msg1 |
 +----+--------+-----------+---------+------+
 | 2 | 1 | 2 | 1 | msg2 |
 +----+--------+-----------+---------+------+
 | 3 | 1 | 1 | 2 | msg3 |
 +----+--------+-----------+---------+------+
 | 4 | 1 | 2 | 1 | msg4 |
 +----+--------+-----------+---------+------+
 | 5 | 1 | 1 | 3 | msg1 |
 +----+--------+-----------+---------+------+
 | 6 | 1 | 3 | 1 | msg2 |
 +----+--------+-----------+---------+------+ 

What I'm doing

I'm using using below query to fetch communication between two users on the basis of enquiries but it is giving two seperate records for same users & enquiry.

SELECT 
 r.enquiry_id, 
 r.from_user, 
 r.to_user, 
 count(1) replies_count 
FROM 
 `replies` r 
GROUP BY 
 r.enquiry_id, r.from_user, r.to_user
ORDER BY 
 r.id DESC

This is the result of the above query

 +--------+-----------+---------+-----+
 | enq_id | from_user | to_user | msg |
 +--------+-----------+---------+-----+
 | 1 | 1 | 2 | 2 |
 +--------+-----------+---------+-----+
 | 1 | 2 | 1 | 2 |
 +--------+-----------+---------+-----+
 | 1 | 1 | 3 | 1 |
 +--------+-----------+---------+-----+
 | 1 | 3 | 1 | 1 |
 +--------+-----------+---------+-----+

And my expected result is like

+--------+--------------+--------------+-----+
| enq_id | participant1 | participant2 | msg |
+--------+--------------+--------------+-----+
| 1 | 1 | 2 | 4 |
+--------+--------------+--------------+-----+
| 1 | 1 | 3 | 2 |
+--------+--------------+--------------+-----+

• McNets: can be more than two distinct users in the conversation?
• Ramesh Mhetre: There will be single row for two users communicating on same enquiry, I've modified my question please check

Answer 1

check it here if it is what you're asking for: http://rextester.com/TVOKS20933

select enquiry_id, from_user, to_user, count(1) msg 
from replies
group by enquiry_id, from_user, to_user
order by id;
+----+--------+-----------+---------+------+
| id | enq_id | from_user | to_user | msg |
+----+--------+-----------+---------+------+
| 1 | 1 | 1 | 2 | msg1 |
+----+--------+-----------+---------+------+
| 2 | 1 | 2 | 1 | msg2 |
+----+--------+-----------+---------+------+
| 3 | 1 | 1 | 2 | msg3 |
+----+--------+-----------+---------+------+
| 4 | 1 | 2 | 1 | msg4 |
+----+--------+-----------+---------+------+
| 5 | 1 | 1 | 3 | msg1 |
+----+--------+-----------+---------+------+
| 6 | 1 | 3 | 1 | msg2 |
+----+--------+-----------+---------+------+ 
+--------+-----------+---------+-----+
| enq_id | from_user | to_user | msg |
+--------+-----------+---------+-----+
| 1 | 1 | 2 | 2 |
+--------+-----------+---------+-----+
| 1 | 2 | 1 | 2 |
+--------+-----------+---------+-----+
| 1 | 1 | 3 | 1 |
+--------+-----------+---------+-----+
| 1 | 3 | 1 | 1 |
+--------+-----------+---------+-----+

• Ramesh Mhetre: This what is I'm doing right now, I want only one row for communication between two users. I have modified my question, please check

Answer 2

A dialogue between two users can be grouped together as a single row if you ensure that the IDs always go in a specific order, for example, lower ID followed by higher ID. For just two elements, you can easily implement such an order using built-in functions LEAST and GREATEST. For instance, if you have rows:

from_user to_user
--------- -------
1 2
2 1
1 2

and perform a query like this:

SELECT
 LEAST(from_user, to_user) AS participant1,
 GREATEST(from_user, to_user) AS participant2
FROM
 ...

the result will be:

participant1 participant2
------------ ------------
1 2
1 2
1 2

If you then use the expressions LEAST(from_user, to_user) and GREATEST(from_user, to_user) as grouping terms instead of simply from_user, to_user, your output will return a single row per each pair of users regardless of which one was from_user and which to_user.

Therefore, you can modify your query like this to achieve the desired output:

SELECT 
 r.enquiry_id, 
 LEAST(r.from_user, r.to_user) AS participant1, 
 GREATEST(r.from_user, r.to_user) AS participant2, 
 count(1) replies_count 
FROM 
 `replies` r 
GROUP BY 
 r.enquiry_id,
 LEAST(r.from_user, r.to_user),
 GREATEST(r.from_user, r.to_user)
ORDER BY 
 MAX(r.id) DESC
;

You can see that I also changed the ORDER BY from r.id to MAX(r.id). While it is true that MySQL's non-strict mode permits you to select non-GROUP BY columns without aggregation, in practice this can be very misleading and cause unpredictable/inconsistent results where the selected column can have multiple values per group. I suggest you always wrap a non-GROUP BY column in an aggregate function when referencing it in SELECT, HAVING or ORDER BY.