CODEWITHSUNDEEP
mysql
PHPhil
PHPhil

Reputation: 1540

Find the unique column combinations

I'm trying to find the unique combination. The sequence is not important so 1 - 3 and 3 - 1 is the same. I'm stuck at:

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

End result:

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

Upvotes: 4

Views: 49

Answers (1)

sgeddes
sgeddes

Reputation: 62861

One option is to use least and greatest with 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)

Upvotes: 7

Related Questions