SQL finding the average of a group by generated column-CodePudding

I have a sakila table set of film (contains film_id) and film_actor(contains actor_id, film_id). id like to find all actors that played in more movies than the average. ive tried using:

select actor_id, count(film_actor.film_id) as film_count
from film, film_actor
where film.film_id = film_actor.film_id
group by actor_id
having film_count > avg(film_count)

I'm getting a

Error Code: 1111. Invalid use of group function

and don't understand why I cant preform the average function.

without the HAVING command I manage to generate

|actor_id| film_count| |--------|-----------| | 1 | 19 | | 2 | 25 | and so on. so why cant I average the film_count column

CodePudding user response：

You should express the overall average using a formal subquery:

WITH cte AS (
    SELECT fa.actor_id, COUNT(fa.film_id) AS film_count
    FROM film f
    INNER JOIN film_actor fa ON f.film_id = fa.film_id
    GROUP BY fa.actor_id
)

SELECT actor_id, film_count
FROM cte
WHERE film_count > (SELECT AVG(t.film_count) FROM cte t);