select max count after group by multiple columns

Question

I have a table with schema and data as follows

id,winner,season  
1, Chelsea, 2014 
2, Chelsea, 2014 
3, Chelsea, 2015 
4, Arsenal, 2014

. .

and so on.

Basically, there is an entry in there for every match of every season.

I need to query and identify the team that won the maximum number of matches for each season.

I got till retrieving the maximum number of matches in each season but I am not able to retrieve the name of the team that had the maximum. Any leads?

with  t1 as
(select count(winner) as cnt, winner, season from matches group by winner,season)
select max(cnt),season from t1 group by season

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, May 21 '18 at 20:41
Group by on multiple columns https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns — Universus, May 21 '18 at 20:43
@JuanCarlosOropeza yes, i would like to show both. I am practicing on 8.0.11. — user3409426, May 21 '18 at 20:51
@DanielMarcus The query works! I m able to see the winner and season. Still figuring out how I can bring the count too with your query! :) — user3409426, May 21 '18 at 21:03

The Impaler · Accepted Answer · 2018-05-21T21:00:56.273

3

This one should work:

select t2.*
from (
select season, max(totalwins) as totalwins from (
select season, winner, count(*) as totalwins
  from matches t
  group by season, winner
) s1 group by season) w join
(select season, winner, count(*) as totalwins
  from matches t
  group by season, winner
) t2 on t2.season = w.season and t2.totalwins = w.totalwins;

Result:

season  winner  totalwins
------  ------- ---------
2014    Chelsea 2
2015    Arsenal 1
2015    Chelsea 1

edited May 21 '18 at 21:00

answered May 21 '18 at 20:55

The Impaler

45,731
9
39
76

This one works! Thanks! Can this be achieved without joins too? – user3409426 May 21 '18 at 21:05
The other option would be to use `WITH` statements instead of joins, but MySQL does not support them as of version 5.x – The Impaler May 21 '18 at 21:07

select max count after group by multiple columns

1 Answers1