MySQL 1064 Error what is the way around solving the syntax error?

Question

I needed some help because im a beginner in MYSQL I have tried to look at other posts for this error have gotten nowhere.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT max(count) from (Select activity, count(*) from FRIENDS group by activity' at line 7

Why is this error being generated and how do i fix this?

SELECT activity
FROM (SELECT activity, count(*) FROM FRIENDS GROUP BY activity) AS getActivities
WHERE count NOT IN (
    SELECT min(count)
        FROM (SELECT activity, count(*) FROM FRIENDS GROUP BY activity ) AS maximum,
    SELECT max(count)
        FROM (SELECT activity, count(*) FROM FRIENDS GROUP BY activity) AS minimum
)

Answer 1

This is your query:

select activity
from (Select activity, count(*)
      from FRIENDS
      group by activity
     ) as getActivities
where count not in (Select min(count) from (Select activity, count(*) from FRIENDS group by activity ) as maximum,
                    SELECT max(count) from (Select activity, count(*) from FRIENDS group by activity) as minimum
                   );

You have multiple errors. For instance:

• count(*) has no column alias. But it needs a name because it is in a subquery.
• The subquery has no table alias.
• The subqueries in the outer where have parentheses.
• The , implies that you intend the subqueries as scalar subqueries, but they can return multiple rows.

I think you intend:

select f.activity
from friends f
group by f.activity
having count(*) > (select count(*)
                   from friends f2
                   group by f2.activity
                   order by count(*) asc
                   limit 1
                  ) and
       count(*) < (select count(*)
                   from friends f2
                   group by f2.activity
                   order by count(*) desc
                   limit 1
                  );