So I have a scheme like the following:
| phone1 | phone2 | phone3 | phone4 | phone5 | phone6 | | ------------- | ----------- | ----------- | ------------- | ----------- | ------------- | | 447488575023 | 07488575023 | 07397005898 | 441535930275 | 01535930275 | 07397005898 | | 01606592390 | 07968233423 | 07968233423 | 01606592490 | 07968233423 | 441606592490 | | 01606592390 | 07968233423 | 07968233423 | 01606592490 | 07968233423 | 441606592490 | | 447544737701 | 07544737701 | 07544737701 | 07544737701 | 07587989521 | 447402204547 | | 447383626815 | 07383626815 | 07383626815 | 07383626815 | 07508889595 | 447412987535 | | 441752313756 | 01752313756 | 07958697492 | 01752313756 | 07746624841 | 447958437692 | | 447784301122 | 07784301122 | 07784301122 | 07784301122 | 07732274851 | 447979879900 |
What I want to achieve is to get the top 6 unique occurring numbers in order of the count across all rows and columns.
Advertisement
Answer
You need to unpivot. In MySQL, this is typically done using union all
:
select phone, count(*) from ((select phone1 as phone from t) union all (select phone2 as phone from t) union all (select phone3 as phone from t) union all (select phone4 as phone from t) union all (select phone5 as phone from t) union all (select phone6 as phone from t) ) p group by phone order by count(*) desc limit 1;