CODEWITHSUNDEEP
mysqlsqldatabase
Tony Sala
Tony Sala

Reputation: 103

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.

Upvotes: -1

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

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;

Upvotes: 2

Related Questions