Error while executing SQL query on database: misuse of aggregate: count()

Question

SELECT title FROM film
WHERE film_id in (
    SELECT count(inventory.inventory_id) as counter
    FROM inventory
    WHERE film_id = film.film_id and counter = 8
)

I am trying to display all movie titles from 'film' that have exactly 8 copies available in the inventory but I can't seem to figure out the proper way of doing it with subqueries.

Answer 1

You must group by film_id in the table inventory and set the condition for the 8 copies available in the HAVING clause:

SELECT title 
FROM film
WHERE film_id in (
  SELECT film_id
  FROM inventory
  GROUP BY film_id
  HAVING COUNT(*) = 8
);

Answer 2

This should do it:

SELECT title
  FROM film
  WHERE film_id in (SELECT FILM_ID
                      FROM (SELECT FILM_ID, COUNT(*) AS COUNTER
                              FROM INVENTORY
                              GROUP BY FILM_ID)
                      WHERE COUNTER = 8)