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   |
+--------+--------------+--------------+-----+

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   |
+--------+-----------+---------+-----+

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.