Mr.Byte Mr.Byte - 7 months ago 15
SQL Question

Group by two column with vice versa value

I have tried to group by with two columns:

SELECT recipient_id, sender_id, count(*) FROM messengers WHERE recipient_id=41 OR sender_id=41 group by recipient_id, sender_id


The output will be as shown below:

enter image description here

How I can combine row 1 and row 2 as a row because recipient_id and sender_id have same value which is 40 and 41.

Answer

One trick you can use here is to GROUP BY the smaller of the two ID columns and the greater of the two ID columns:

SELECT LEAST(recipient_id, sender_id), GREATEST(recipient_id, sender_id), COUNT(*)
FROM messengers
WHERE recipient_id = 41 OR sender_id = 41
GROUP BY LEAST(recipient_id, sender_id), GREATEST(recipient_id, sender_id)
Comments