MySQL, join to find only max transaction count based of group

Question

I am trying to find the max of transactions count based on type for each user.

id	user_id	type
1	1	A
2	1	B
3	1	C
4	1	A
5	2	B
6	2	C
7	2	C
8	2	C

I am expecting the output to be:

user_id	type	count
1	A	2
2	C	3

SELECT 
    DISTINCT(t.user_name), t.discom, c.*
FROM transactions AS t
LEFT JOIN (
    SELECT MAX(id) AS id, user_id, COUNT(id) AS `count` FROM transactions
    GROUP BY user_name, discom
) AS c ON c.user_id = t.user_id
GROUP BY t.user_name
ORDER BY c.count DESC

Although, the query is running, but its taking too much time(in minutes) just to fetch the data of mere 3500 users.

that version of mysql is long past end of life; consider it extremely insecure and upgrade as soon as you can — ysth
– ysth, Commented May 13 at 12:07
I am aware of that, but its not up to me. This is what the management has to decide. — Mr.Singh
– Mr.Singh, Commented May 14 at 6:14
just make sure they are aware it has received no security updates in almost 5 years; in today's world that's basically the same as just openly publishing all your data (as well as inviting hackers to delete it and hold it for ransom) — ysth
– ysth, Commented May 14 at 15:59

Akina · Accepted Answer · 2025-05-13 07:16:02Z

3

The solution:

SELECT user_id, subquery_1.type, cnt
FROM (
  SELECT user_id, type, COUNT(*) cnt
  FROM transactions
  GROUP BY user_id, type
  ) subquery_1
JOIN ( 
  SELECT user_id, MAX(cnt) cnt
  FROM (
    SELECT user_id, type, COUNT(*) cnt
    FROM transactions
    GROUP BY user_id, type
    ) subquery_2
  GROUP BY user_id
  ) subquery_3 USING (user_id, cnt);

On your version 5.5.62-cll the next query should produce correct output in most cases:

SELECT user_id, type, MAX(cnt)
FROM (
  SELECT user_id, type, COUNT(*) cnt
  FROM transactions
  GROUP BY user_id, type
  ORDER BY user_id, cnt DESC
  ) subquery
GROUP BY user_id

Pay attention - this is a trick which DOES NOT GUARANTEE that the output is correct.

fiddle

edited May 13 at 7:16

answered May 13 at 6:42

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Mr.Singh May 13 at 7:23

Ok, I'll try. Thank you

blabla_bingo May 13 at 7:42

The second solution violates 'ONLY_FULL_GROUP_BY' rule if SQL_MODE has it.

Akina May 13 at 7:51

@blabla_bingo On the OP version this mode is not set by default.

blabla_bingo May 13 at 7:58

OK, then what do you mean by DOES NOT GUARANTEE that the output is correct ? Under what circumstance could things go wrong ? The only issue I find is unlike the first solution, the second one only displays one type for a user_id even if multiple types for the same user_id have the same max count .

Mr.Singh May 13 at 8:31

Thank you, it worked. I tried both queries. However, I prefer the shorter one as its running faster, but output of both is same. Many thanks again :)

|

ysth · Accepted Answer · 2025-05-13 11:51:07Z

1

This is a good candidate for this approach, where what you want the max of is concatenated with the other data you need:

select
  user_id,
  substr(max(count_type),10) as 'type',
  left(max(count_type),9)+0 as 'count'
from (
  select user_id, concat(lpad(count(*),9,'0'),type) count_type
  from transactions
  group by user_id,type
) type_counts
group by user_id

https://dbfiddle.uk/WT7xGJIm

answered May 13 at 11:51

ysth

99.1k6 gold badges126 silver badges219 bronze badges

1 Comment

Akina May 14 at 6:22

Oh! the old grandmother's method from Oracle... I thought no one remembered it.

Lajos Arpad · Accepted Answer · 2025-05-13 07:59:38Z

0

The query

select user_id, type, count(*) as cnt
from transactions t1
group by user_id, type
having count(*) = (
    select count(*)
    from transactions t2
    where t1.user_id = t2.user_id
    group by user_id, type
    order by count(*) desc
    limit 1
);

Explanation:

in the inner query we get the maximum count of groups by user_id and type that matches the user_id from the outer query
in the outer query we group by user_id, type and ensure in the having clause to have the count match the maximum count computed in the inner query

Fiddle.

answered May 13 at 7:59

Lajos Arpad

80.4k42 gold badges123 silver badges234 bronze badges

1 Comment

Mr.Singh May 13 at 8:47

The query is producing the same result, but taking 3x time.

Collectives™ on Stack Overflow

MySQL, join to find only max transaction count based of group

3 Answers 3

13 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

13 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related