Make an sql group by non commutative-CodePudding

Hi I have a question for some sql GROUP BY commands.

I have a table which looks like this

messages(#id, from_user, to_user, message)

If from_user (of id 1) sends a message to to_user (of id 2) it makes this :

id	from_user	to_user	message
1	1	2	blabla

Now let's assume we have more data :

id	from_user	to_user	message
1	1	2	blabla
2	7	8	blabla
3	9	10	blabla
4	11	12	blabla
5	2	1	blabla

In the above table you can see that user 1 sent a message to user 2 AND user 2 sent a message to user 1.

If I want the count of all messages for each "conversation" I guess that I have to do a GROUP BY aggregation like follow :

SELECT from_user, to_user, COUNT(*) AS nb_messages
FROM `message`
GROUP BY from_user_id, to_user_id

But with this operation it gives :

from_user	to_user	nb_messages
1	2	1
7	8	1
9	10	1
11	12	1
2	1	1

What I would like is to "merge" the lines where (from_user=1 and to_user=2) and (from_user=2 and to_user=1) so it would give this view :

from_user	to_user	nb_messages
1	2	2
7	8	1
9	10	1
11	12	1

Or even better would be :

from_user	to_user	nb_messages_sent	nb_messages_received
1	2	1	1
7	8	1	0
9	10	1	0
11	12	1	0

If you have any idea of how to do this, thanks in advance for your help.

CodePudding user response：

Count by least, greatest conditionally

select 
    least(from_user, to_user) from_user, 
    greatest(from_user, to_user) to_user,
    count(case when from_user < to_user then id end) as from_messages,
    count(case when from_user > to_user then id end) as to_messages
from `message`
group by least(from_user, to_user), greatest (from_user, to_user)