I have this one table:

ColumnA, ColumnB
C,F
F,C
A,C
C,A
C,F
C,F
C,A
C,F
F,C
X,Y

and I need the query to get this output:

ColumnA, CoulmnB, Count
C,F,5
A,C,3
X,Y,1

CodePudding user response：

Use least() and greatest() to always get column pair values in alphabetic order:

select least(ColumnA, ColumnB), greatest(ColumnA, ColumnB), count(*) cnt
from tablename
group by least(ColumnA, ColumnB), greatest(ColumnA, ColumnB)
order by cnt desc