CODEWITHSUNDEEP
phpmysqlalgorithmstatistics
GGio
GGio

Reputation: 7653

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

I have the following table structure:

CREATE TABLE test(
   myID INT,
   num1 INT,
   num2 INT,
   num3 INT,
   PRIMARY KEY (myID)
)engine=innodb;

now i have the following data in the table:

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

now i need to run 2 queries, first query runs to select all numbers and on PHP side i count the number of times each number appeared (frequencies), the second query should select the second most frequent number. First query:

$numfreq = PDO->prepare('
                SELECT num1, num2, num3
                FROM 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 from the table. Now for the second part

THIS IS WHERE I NEED HELP:

I get the most frequent appeared number in this case its 27 since its frequency is 3, I need to select that 1 number that appears the most next to 27 means i need to get somehow number 15 since it appears twice next to 27.

i can probably figure out algorithm on PHP side but i was wondering if its possible to do it using query?

so the final result would be:

most frequent number: 27
most frequent number 27 combined with 15 appears 2 times and is most frequent combination.

Upvotes: 0

Views: 919

Answers (1)

Neville Kuyt
Neville Kuyt

Reputation: 29639

select val, count(val) as frequency
from 
(select num1 as val from test
 union all
 select num2 as val from test
 union all
 select num3 as val from test
 ) as b
group by val
order by frequency desc
limit 2

Sqlfiddle here.

The inner query converts the three columns into a result set with just one column - highlight the inner query, and you'll see how it works. We then use that result set as the source for the counting/ordering query.

Upvotes: 1

Related Questions