CODEWITHSUNDEEP
mysql
AlfaTeK
AlfaTeK

Reputation: 7775

Mysql query to count top ocurrences in 3 columns (considering it as 1 column only)

I have a table like this: user_id hobbie1 hobbie2 hobbie3

1  ski          soccer  tv
1  skating      tv      sleep
1  tv           ski     sleep
1  tv           read    movies
(...)

And I want to get, for that user_id, the top 10 hobbies he has, ordered by occurences number. Yes, I know the database seems odd in terms of normalization, but this is a simplified example of the real thing :) The table has several rows with the same user_id and 3 columns with its hobbies.

In that example I should be able to get: hobbie count tv 4 sleep 2 ski 2 (...)

So, I'm thinking that I need to convert 1 row (1 ski soccer tv) to 3 rows (1 ski | 1 soccer | 1 tv) to then do my normal sql count, etc.

My solution is:

select hobbie, count(hobbie) from (
      (select hobbie1 as hobbie from hobbies)
    union all
      (select hobbie2 as hobbie from hobbies)
    union all
      (select hobbie3 as hobbie from hobbies)
    ) AS b 
    group by b.hobbie order by 2 desc limit 10

But that doesn't seem to be optimized and uses UNION to solve the problem. Any better solution?

Upvotes: 1

Views: 194

Answers (2)

ndp
ndp

Reputation: 21996

I like this problem, but If the goal is to do in a single query, I can't think of much better solution than you have.

To me, it begs for thinking out of the box:

  • The unhelpful answer to "normalize" the data is, well, unhelpful. Obviously that's not an option.
  • How often do you need this data? How up to date does it need to be? Perhaps every night you could extract the needed data into a normalized table and run queries off of that? This is a common strategy for aggregate data that is just too hard to calculate.

Upvotes: 1

RDL
RDL

Reputation: 7961

There really isn't a good/nice way via one sql statement only.

Have a query that grabs all of the the hobbies and then loops over the result tabulating them. Something like (in php):

select hobbie1, hobbie2, hobbie3 from hobbies where user = ##

$tallies = array();
foreach ($result as $rs) // loops through the rows
  for ($i = 1; $i <= 3; $i ++) // loops through the columns
    if (array_key_exists($rs['hobbie'.$i], $tallies)
      $tallies[$rs['hobbie'.$i]] += 1;
    else
      $tallies[$rs['hobbie'.$i]] = 1;

Then sort the array by values using asort()

asort($tallies);

They will now be smallest to largest so lets get the list of items and reverse it:

$tallies = array_reverse(array_keys($tallies));

You now have an array with the most popular hobby to the least. Hope that helps.

Upvotes: 1

Related Questions