Skip to content
Advertisement

MySQL Scoring of values from select across rows and multiple columns

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;
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement