algorithm/query to get statistical data from table using php/mysql -

algorithm/query to get statistical data from table using php/mysql -

i have next table structure:

create table test( myid int, num1 int, num2 int, num3 int, primary key (myid) )engine=innodb;

now have next info in table:

myid num1 num2 num3 1 15 27 98 2 27 38 66 3 15 27 77

now need run 2 queries, first query runs select numbers , on php side count number of times each number appeared (frequencies), sec query should select sec frequent number. first query:

$numfreq = pdo->prepare(' select num1, num2, num3 test '); $numfreq->execute(); $allnums = array(); while ($row = $numfreq->fetch(pdo::fetch_assoc)) { $allnums[] = intval($row['num1']); $allnums[] = intval($row['num2']); $allnums[] = intval($row['num3']); } $numfrequencies = array_count_values($allnums); arsort($numfrequencies);

this correctly returns frequencies of each number table. sec part

this need help:

i frequent appeared number in case 27 since frequency 3, need select 1 number appears next 27 means need somehow number 15 since appears twice next 27.

i can figure out algorithm on php side wondering if possible using query?

so final result be:

most frequent number: 27 frequent number 27 combined 15 appears 2 times , frequent combination.

select val, count(val) frequency (select num1 val test union select num2 val test union select num3 val test ) b grouping val order frequency desc limit 2

sqlfiddle here.

the inner query converts 3 columns result set 1 column - highlight inner query, , you'll see how works. utilize result set source counting/ordering query.

php mysql algorithm statistics