I have a table 'films' containing data regarding films (a unique 'film_id', a 'title', a box-office gross result in $M ('box_result') etc.), and a table 'estimates' containing box-office gross estimates for these films. The latter table has multiple estimates for each film, with fields for the estimate value ('estimate'), and the id of the film for to which the estimate pertains ('film_id').
right now I have a functional query that retrieves fields from the film table, along with the average of all estimates for that film from the estimates table. It looks like this:
SELECT title, trailer, synopsis, estimate_close_date, release_date, poster, films.film_id, box_result, ROUND(AVG( e1.estimate )) AS average
FROM films, estimates AS e1
WHERE box_result IS NOT NULL
AND films.film_id = e1.film_id
AND box_result > 50
GROUP BY films.film_id
ORDER BY box_result_date DESC
LIMIT 10
I would like to alter this query to return only those films for which the error of the average estimate is below a specific value (say 30%). So I want a condition something like:
ABS( ( AVG( e1.estimate ) - films.box_result )/films.box_result ) < 0.3
where the estimates used in this calculation are only those corresponding to the relevant film (ie, e1.film_id = films.film_id)
What's the best way to modify this query to limit the results to this subset?
So far I've tried these:
SELECT title, trailer, synopsis, estimate_close_date, release_date, poster, films.film_id, box_result, ROUND(AVG( e1.estimate )) AS average
FROM films, estimates AS e1
WHERE box_result IS NOT NULL
AND films.film_id = e1.film_id
AND ROUND(AVG( e1.estimate )) > 50
AND ABS( ( AVG( e1.estimate ) - films.box_result )/films.box_result ) < 0.3
GROUP BY films.film_id
ORDER BY box_result_date DESC
LIMIT 10
and
SELECT title, trailer, synopsis, estimate_close_date, release_date, poster, films.film_id, box_result, ROUND(AVG( e1.estimate )) AS average
FROM films, estimates AS e1
WHERE box_result IS NOT NULL
AND films.film_id = e1.film_id
AND ROUND(AVG( e1.estimate )) > 50
GROUP BY films.film_id
HAVING ABS( ( AVG( e1.estimate ) - films.box_result )/films.box_result ) < 0.3
ORDER BY box_result_date DESC
LIMIT 10
but in both cases I get the error 'invalid use of group function'
