0

I currently have a table like below

Name Season count(*)
Name1 2020 2
Name2 2020 3
Name3 2020 4****
Name1 2021 5**
Name2 2021 3
Name3 2021 1
Name1 2022 3
Name2 2022 4**

And I want a table like

Name Season count(*)
Name3 2020 4
Name1 2021 5
Name2 2022 4

I've tried using

select max(count(*)) from ~~

, and I found that it's not gonna work. Can you guys give me some adivice?

nbk
  • 45,398
  • 8
  • 30
  • 47
user21222773
  • 1
  • You can't nest aggregates. You need to do `COUNT(*)` in a subquery, the `MAX()` in the main query. – Barmar Feb 16 '23 at 00:17
  • If you want to know the year with the max, you need to use [max-row-per-group](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql?rq=1) using the count subquery as the table you're doing this to. A CTE or window functions will make this easier. – Barmar Feb 16 '23 at 00:19

0 Answers0