Mysql group by multiple combinations 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 &amp; 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   |
+--------+--------------+--------------+-----+
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   |
+--------+-----------+---------+-----+