Find unique combinations of columns

Question

I am trying to find a unique combination. The sequence is not important, therefore 1 - 3 and 3 - 1 are the same. I am stuck in:

SELECT column1, column2, count(*)
FROM testTable
GROUP BY column1, column2

Example:

id      column1     column2
1       1           3
2       3           2
3       3           1
4       1           2
5       2           1
6       2           3
7       1           2
8       3           2
9       1           3
10      3           2

Final result:

column1     column2     count
1           2           3
1           3           3
2           3           4

Answer 1

One option is to use leastand greatestwith aggregation:

select least(column1,column2) as column1, 
       greatest(column1,column2) as column2, 
       count(*) as cnt
from testtable
group by least(column1,column2), greatest(column1,column2)

• SQL Fiddle Demo