Lannister Lannister - 5 months ago 7
SQL Question

How to get most occurences of rows for every user in mysql

user_id category suburb dated walk_time
1 experience US 2016-04-09 5
1 discovery US 2016-04-09 5
1 experience UK 2016-04-09 5
1 experience AUS 2016-04-23 10
2 actions IND 2016-04-15 2
2 actions IND 2016-04-15 1
2 discovery US 2016-04-21 2
3 discovery FR 2016-04-12 3
3 Emotions IND 2016-04-23 3
3 discovery UK 2016-04-12 4
3 experience IND 2016-04-12 3


I am trying to get every users most used category,suburb,dated,walk_time

so resulting table would be

user_id category suburb dated walk_time
1 experience US 2016-04-09 5
2 actions IND 2016-04-15 2
3 discovery IND 2016-04-12 3


The query I am trying here is

select user_id,
substring_index(group_concat(suburb order by cnt desc), ',', 1) as suburb_visited,
substring_index(group_concat(category order by cct desc), ',', 1) as category_used,
substring_index(group_concat(walk_time order by wct desc), ',', 1) as walked,
substring_index(group_concat(dated order by nct desc), ',', 1) as dated_at
from (select user_id, suburb, count(*) as cnt,category, count(*) cct, walk_time, count(*) wct, dated,count(*) nct
from temp_user_notes
group by user_id, suburb,category,walk_time,dated
) upv
group by user_id;

Answer
SELECT user_id,
      (SELECT category FROM temp_user_notes t1
       WHERE t1.user_id = T.user_id
       GROUP BY category ORDER BY count(*) DESC LIMIT 1) as category,
      (SELECT suburb FROM temp_user_notes t2
       WHERE t2.user_id = T.user_id
       GROUP BY suburb ORDER BY count(*) DESC LIMIT 1) as suburb,
      (SELECT dated FROM temp_user_notes t3
       WHERE t3.user_id = T.user_id
       GROUP BY dated ORDER BY count(*) DESC LIMIT 1) as dated,
      (SELECT walk_time FROM temp_user_notes t4
       WHERE t4.user_id = T.user_id
       GROUP BY walk_time ORDER BY count(*) DESC LIMIT 1) as walk_time
FROM (SELECT DISTINCT user_id FROM temp_user_notes) T

http://sqlfiddle.com/#!9/8aac6a/19

Comments