name of subquery not accessible by where clause SELECT
  NAME,
  (SELECT COUNT(1) FROM MOVIERENTED WHERE MOVIERENTED.MOVIEID=MOVIE.MOVIEID) AS RENTCOUNT
FROM MOVIE
ORDER BY RENTCOUNT;


Some movies in this database have never been rented, how can I select only the movies that have been rented outside of the subquery?

I have tried adding WHERE RENTCOUNT &gt; 0 after the FROM MOVIE statement.


  ORA-00904: "RENTCOUNT": invalid identifier
Simiply use an Inner Join (your current query using a Scalar Subquery is similar to an Outer Join):

SELECT
  MOVIE.NAME,
  COUNT(*) AS RENTCOUNT
FROM MOVIE 
JOIN MOVIERENTED 
ON MOVIERENTED.MOVIEID=MOVIE.MOVIEID
GROUP BY MOVIE.NAME, MOVIE.MOVIEID
ORDER BY RENTCOUNT;