0
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

0
2

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;
0
1

One option would be to add a level of nesting

select *
  from (SELECT NAME,
               (SELECT COUNT(1) 
                  FROM MOVIERENTED 
                 WHERE MOVIERENTED.MOVIEID=MOVIE.MOVIEID) AS RENTCOUNT
          FROM MOVIE) optional_alias
 where rentcount > 0
 ORDER BY RENTCOUNT;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.