Name of subquery not accessible by where clause

oracle-11g

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 > 0 after the FROM MOVIE statement.

ORA-00904: "RENTCOUNT": invalid identifier

Best Answer

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;
Related Question