sqlsqlitegroup-byhavingsql-in

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

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.

Solution

  • 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
);