Display count of column excluding min and max values

Question

I want to count how many unique occurrences of an activity occurs in the table (FRIENDS) below. Then, I want to print the activities whom which their occurrences are not the maximum or minimum value of all occurrences.

***ID/Name/Activity***
1/James/Horse Riding
2/Eric/Eating
3/Sean/Eating
4/John/Horse Riding
5/Chris/Eating
6/Jessica/Paying

Ex:

Horse Riding occur 140 times
Playing occurs 170 times
Eating occurs 120 times
Walking occurs 150 times
Running occurs 200 times

The max occurrence here is Running, occurring 200 times, and the minimum occurrence here is Eating, occurring 120 times.

Therefore, I want to display

Horse Riding
Playing
Walking

In no particular order.

This is a code I have so far, but I keep getting a syntax error. When I don't get a syntax error, I get a "Every derived table must have its own alias error." I am new to SQL so I appreciate any advice I can get.

SELECT ACTIVITY, count(ACTIVITY) as Occurences FROM FRIENDS,
(SELECT MAX(Occur) AS Ma,MIN(Occur) AS Mi FROM (SELECT ACTIVITY, count(ACTIVITY) as Occur
FROM FRIENDS GROUP by City)) as T 
GROUP BY City HAVING Occurences!=T.Ma AND Occurences!=T.Mi ORDER BY Occurences DESC

Answer 1

In MySQL 8.0, you can do this with aggregation and window functions:

select *
from (
    select activity, count(*) cnt,
        rank() over(order by count(*)) rn_asc,
        rank() over(order by count(*) desc) rn_desc
    from mytable 
    group by activity
) t
where rn_asc > 1 and rn_desc > 1

The subquery counts the occurences of each activity, and ranks them in both ascending and descending oders. All that is left to do is exclude the top and bottom records. If there are top ties (or bottoms), the query evicts them.

In earlier versions, an option is a having clause:

select activity, count(*) cnt
from mytable t
group by activty
having count(*) > (select count(*) from mytable group by activity order by count(*) limit 1)
   and count(*) < (select count(*) from mytable group by activity order by count(*) desc limit 1)