Group by either column

Question

Sample Table:

User1 | User2
------+------
123   | 555
123   | 1
123   | 2
456   | 2
555   | 456
12    | 123
12    | 456

Input: I enter the list (123,456) to look at rows containing either of those values. Then I want MySQL to check the opposite/other column in that row, and group the output by that value.

Output:

User | Count(*)
-----+---------
555  | 2
2    | 2
1    | 1

555 count is 2 because row 123, 555 and row 555, 456 both contain one of the inputs: 123 and 456.

I've tried looking at the CASE keyword, because the obvious obstacle here is grabbing the opposite/remaining column and using that as one of the returned values.

Completely wrong, but one of my half-finished approaches.

SELECT user, count(*)
FROM friendships
WHERE User1 IN (123,456) AS user 
   OR User2 IN (123,456) AS user

Answer 1

the tricky part with this is your criteria is the IN() is looking in the users1 column and then tries to find duplicates in the user2 column... so you need a join with a UNION

SELECT user2, COUNT(*)
FROM
(   SELECT user1, user2
    FROM friends f
    WHERE f.user1 IN(123, 456)

    UNION ALL

    SELECT f.user1, f.user2
    FROM friends f
    JOIN friends f1 ON f1.user1 = f.user2
    WHERE f.user1 IN(123, 456)
)t
GROUP BY user2;

WORKING FIDDLE