Group by column pairs without considering their order-CodePudding

I have a table like this:

Name1	Name2	Address1	Address2
Jane	Mary	1st Street	2nd Street
Mary	Jane	2nd Street	1st Street
Aubery	Mary	3rd Street	2nd Street
Sindy	Simon	4th Street	5th Street

I would like row 1 and 2 to be captured as the same in a GROUP BY statement, that is, row 1 and 2 represents the same information but in a different order (So their count would be 2) with the two pairs being Name1, Address1 & Name2, Address2.

How would I go about this?

CodePudding user response：

We can aggregate using a LEAST/GREATEST trick:

SELECT
    LEAST(Name1, Name2) AS Name1,
    GREATEST(Name1, Name2) AS Name2,
    LEAST(Address1, Address2) AS Address1,
    GREATEST(Address1, Address2) AS Address2,
    COUNT(*) AS cnt
FROM yourTable
GROUP BY 1, 2, 3, 4;

Demo