How do I find the average of a count in MySQL?

Question

I need to find the average of a count as described below, but when the code is executed, error 1111 is returned. I know I need a nested query to solve the issue, but I don't know how to format it.

SELECT COUNTRY.COUNTRY_NAME, CITY.CITY_NAME, COUNT(CUSTOMER.CITY_ID)
FROM COUNTRY
JOIN CITY
ON COUNTRY.ID = CITY.COUNTRY_ID
JOIN CUSTOMER
ON CITY.ID = CUSTOMER.CITY_ID
GROUP BY CITY.CITY_NAME
HAVING COUNT(CUSTOMER.CITY_ID) > AVG(COUNT(CUSTOMER.CITY_ID))
ORDER BY COUNTRY.COUNTRY_NAME ASC;

Answer 1

等待大神答复