GROUP Combinations of Columns, not Permutations

Question

On a website I've been working on users are allowed to submit data into a table, and when they submit the data, they are asked for a partner name, since two people work on acquiring the data. I am attempting to create a high score table, listing the top partnerships, AKA the combination(not permutation) of partner and submitter that appears most frequently on the table.

The GROUP command works great for this, but I've hit a little snag with the combination/permutation issue. Currently, when I group them, it only checks for permutations of submitters and partners, not combinations. The issue is that often a partnership chooses to alternate between one person being the submitter, and the other being the partner, so I have actually two possible permutations of GROUPs which I can pull.

Currently I have code which will pull one high score table of Submitter and Partner permutations, and another high score table of Partner and Submitter permutations. I need to join these results(possibly ungrouped and non-ordered) when partner=submitter or submitter=partner, and then group them and order then in descending order.

SELECT submitter, partner, COUNT(*) FROM submissions GROUP BY submitter, partner;

The above code will return the table with the counts of the specific permutation of submitter and partner, but if the same two people are partner and submitter, just switched, they are not counted as the same group.

Anyone know the code for this?

Answer 1

Not the most pretty solution, but it does provide you the answer you want:

SELECT Person1, Person2, COUNT(*) FROM (
    SELECT 
        CASE WHEN submitter < partner THEN submitter ELSE partner END AS Person1,
        CASE WHEN submitter >= partner THEN submitter ELSE partner END AS Person2
    FROM submissions
) Q
GROUP BY Person1, Person2