Search code examples
mysqlsqlgroup-bycountgreatest-n-per-group

What is the correct solution for the below SQL query?

Print name of all activities with neither maximum nor minimum number of participants

I have tried the below query but is giving me error:

select ACTIVITY
from (select ACTIVITY, count(*) as cnt,
             max(count(*)) as max_cnt,
             min(count(*)) as min_cnt
      from FRIENDS GROUP BY ACTIVITY) FRIENDS
where cnt not in (max_cnt, min_cnt);

ERROR: ERROR 1111 (HY000) at line 1: Invalid use of group function MYSQL VERSION: 8

Solution

  • Presumably, you are not running MySQL 8.0 (otherwise the answer given on your previous question would have just worked).

    In earlier versions, you can do:

    select activity, count(*) no_activities
from friends
group by activity
having 
        count(*) > (select count(*) from friends group by activity order by count(*)  asc limit 1)
    and count(*) < (select count(*) from friends group by activity order by count(*) desc limit 1)