CODEWITHSUNDEEP
mysqlsqldatabase
user1294914
user1294914

Reputation:

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.

Upvotes: 3

Views: 453

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522211

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)

Upvotes: 4

Related Questions