How to select most frequent value after group by

Question

I'm trying to categorize a certain entity, let's say, a User, with the most frequent value associated in a table to this entity, let's say, an Emotion.

Thus,a User will be categorized as "Sad" when he logs mostly the value "Sad", or "Happy" when he logs mostly the value "Happy".

An example of the origin table:

user_id	emotion	registered_on
"PAM"	"SAD"	2021-04-05
"PAM"	"HAPPY"	2021-04-06
"PAM"	"HAPPY"	2021-04-07
"TIM"	"SAD"	2021-04-06
"TIM"	"SAD"	2021-05-01
"TIM"	"HAPPY"	2021-06-05

the result I'm looking for:

user_id	year	emotion
"TIM"	2021	"SAD"
"PAM"	2021	"HAPPY"

I'm stuck with Mysql 5.7 so I cannot use window functions.

this is the select I came up with, was planning on using it as a subquery to choose from, but now I'm stuck as to how to select only the rows with the higher value in how_many_times for each user.

select
    user_id,
    YEAR(MAX(registered_on)) as year,
    emotion,
    COUNT(user_id) as how_many_times
from users_emotions
group by user_id, emotion
order by user_id, how_many_times desc

can anyone help?

This is the fiddle: https://www.db-fiddle.com/f/bQnSQZpAorT48Rv2DRMjxS/2

Edit:

So I was almost there, the answer marked as duplicate helped me, not the one marked as the right one, but the one by Vishal Kumar:

(eliminated year for clarity)

select ordered.user_id, SUBSTRING_INDEX(GROUP_CONCAT(ordered.emotion order by ordered.how_many_times desc), ',',1) as emotions from 
(select
    user_id,
    COUNT(user_id) as how_many_times,
    emotion
from users_emotions
group by user_id, emotion) as ordered
group by ordered.user_id
;

I understand it's not perfect, because in case of a tie, it just picks one, but it does what I need!

https://www.db-fiddle.com/f/qyj341zapSob6pvAkcw7Fd/0

You say you're stuck on MySQL 5.7, but _why_ are you stuck on 5.7? — Dai, Mar 03 '23 at 19:12
"...I'm stuck with Mysql 5.7..." -- MySQL 5.7 is reaching end of life 7 months from now. Time to upgrade. — The Impaler, Mar 03 '23 at 19:12
In your real case do you know the overall number of different emoticons at design time? — PM 77-1, Mar 03 '23 at 19:16
See [this answer](https://stackoverflow.com/a/30269273/2055998) and scroll to `MySQL 5.x` section. — PM 77-1, Mar 03 '23 at 19:20
@Dai because I have no saying in what goes into the production server — Cristina Cordero, Mar 04 '23 at 12:02

nbk · Answer 1 · 2023-03-03T19:46:41.080

The query would differ much from MySQL 5.x to MySQL 8, you onoly would use CTE's

With only two emotions this is quite easy

Query #1

SELECT

    user_id,year_emotion,
    CASE WHEN how_many_happy = how_many_unhappy THEN 'middle' 
    WHEN how_many_happy > how_many_unhappy THEN 'HAPPY'
    ELSE 'SAD' END emotions
FROM    
(select
    user_id,
    YEAR(registered_on) year_emotion,
    SUM( emotion =  "HAPPY") as how_many_happy,    
    SUM( emotion =  "SAD") as how_many_unhappy
from users_emotions
group by user_id, YEAR(registered_on)) t1
ORDER  BY user_id
;

user_id	year_emotion	emotions
1	2021	SAD
2	2021	middle
3	2021	SAD

View on DB Fiddle

with 3 it gets more complicated

Query #1

SELECT

    user_id,year_emotion,
    CASE WHEN how_many_happy = how_many_unhappy 
          AND  how_many_happy = how_many_confused THEN 'middle' 
    WHEN how_many_happy >= how_many_unhappy THEN 
       CASE  WHEN how_many_happy > how_many_confused then   'HAPPY'
             ELSE 'COnfused' END 
    ELSE 
    CASE WHEN how_many_unhappy > how_many_confused then   'SAD'
    ELSE 'COMNFUSED'
     END 
     END emotions
FROM    
(select
    user_id,
    YEAR(registered_on) year_emotion,
    SUM( emotion =  "HAPPY") as how_many_happy,    
    SUM( emotion =  "SAD") as how_many_unhappy
 ,SUM( emotion =  "CONFUSED") as how_many_confused
from users_emotions
group by user_id, YEAR(registered_on)) t1
ORDER  BY user_id
;

user_id	year_emotion	emotions
1	2021	SAD
2	2021	HAPPY
3	2021	COMNFUSED

View on DB Fiddle

thanks! but ideally, the user can write whatever they want, so I can't take for granted what they will write. Should have clarified that. — Cristina Cordero, Mar 04 '23 at 11:56
you can write a dynamic query, that first get all the emotions and make the query alone, but it is of course tricky — nbk, Mar 04 '23 at 16:43
but sql is not a dataware house, it will ponly find data, that you know that exist, but usually for a study you will give a number of emotions to choose from, that makes that makes the query deterministic — nbk, Mar 04 '23 at 16:51

XMehdi01 · Answer 2 · 2023-03-03T19:47:45.570

You could groupe the data by columns user_id, Year, and emotion and then selecting the row with the highest count for each user_id, Year, and emotion combination using the ROW_NUMBER() window function. The resulting output shows the user_id, Year, and emotion for each row with the highest count:

Query:

SELECT
  user_id,
  Year,
  emotion
FROM
  (
    SELECT
      user_id,
      DATE_FORMAT(registered_on, '%Y') AS Year,
      emotion,
      COUNT(*) AS count,
      ROW_NUMBER() OVER(
        PARTITION BY user_id
        ORDER BY
          COUNT(*) DESC
      ) rn
    FROM
      users_emotions
    GROUP BY
      user_id,
      Year,
      emotion
  ) t
WHERE
  rn = 1

View on DB Fiddle

score 0 · Answer 3 · answered Mar 03 '23 at 20:19

Well, if it's MysSql 5.7, then you can not use window functions and common table expressions. So, you have to use derived tables (subqueries) few times:

select t1.*
from (
 select 
    user_id, 
    emotion,
    YEAR(registered_on) as regyear,
    COUNT(*) as cnt
 from users_emotions
 group by 1, 2, 3
) as t1
where t1.cnt =  (
  select 
  MAX(cnt) as mcnt
  from (
    select 
      user_id, 
      emotion,
      YEAR(registered_on) as regyear,
      COUNT(*) as cnt
    from users_emotions
    group by 1, 2, 3) as sq
);

Updated Sql fiddle

score 0 · Answer 4 · answered Mar 04 '23 at 02:27

if you have a small number of pre-defined emotions (categories), then nbk's method will perform much better than this.

This will work for any number of possible emotions but uses a slightly arbitrary e1.emotion > e2.emotion to break any ties:

SELECT e1.user_id, e1.reg_year AS year, e1.emotion
FROM (
    SELECT
        user_id, 
        emotion,
        YEAR(registered_on) as reg_year,
        COUNT(*) as cnt
    FROM users_emotions
    GROUP BY 1, 2, 3
) e1
LEFT JOIN (
    SELECT
        user_id, 
        emotion,
        YEAR(registered_on) as reg_year,
        COUNT(*) as cnt
    FROM users_emotions
    GROUP BY 1, 2, 3
) e2
    ON e1.user_id = e2.user_id
    AND e1.reg_year = e2.reg_year
    AND (e1.cnt < e2.cnt OR
         e1.cnt = e2.cnt AND e1.emotion > e2.emotion)
WHERE e2.user_id IS NULL

Here's a db<>fiddle

score 0 · Answer 5 · answered Mar 04 '23 at 02:50

Try the following aggregation with a correlated subquery in the having clause:

select user_id, 
       emotion, 
       year(max(registered_on)) year_
from users_emotions ue
group by user_id, emotion
having count(*) = 
  (
    select max(cnt)
    from
    (
      select user_id, emotion, count(*) cnt
      from users_emotions
      group by user_id, emotion
    ) mx
    where mx.user_id = ue.user_id
  )
order by user_id

demo

How to select most frequent value after group by

5 Answers5