Merging two rows into one
mysql, sql
Solution
SELECT LEAST(user1, user2), GREATEST(user1, user2), SUM(count) AS count
FROM yourtable
GROUP BY LEAST(user1, user2), GREATEST(user1, user2)
Problem
Consider the following table. It describes how many times user1 has started a conversation with user2 ("A started a conversation with B, 5 times"): ``` user1 user2 count ------------------- A B 5 A C 2 B A 6 B C 1 C A 9 C B 4 ``` However, I would like to merge rows between similar users. So user1:A and user2:B is the same as user1:B and user2:A, leading to the following result: ``` user1 user2 count ------------------- A B 11 (5 + 6) A C 11 (2 + 9) B C 5 ``` My first thought was to SELECT the table to PHP, loop through it, add rows and INSERT the result back into a new table. But this seems very redundant (and slow, since the table contains thousands of records). Is this also possible to do with (My)SQL?