0

I am trying to write a SQL query where I have to select title, year and take the movie cost and divide by the price rent fee for each movie that has a price.

PRICE to MOVIE entity is a 1:M, therefore PRICE_CODE is the FK in entity MOVIE.

This is what I have gotten so far but it keep stating that my operator is invalid.

Can anyone help?

SELECT movie_title, movie_year, movie_cost
FROM MOVIE
JOIN PRICE
ON movie.price_code = price.price_code
WHERE (movie_cost)/(price_rentfee);
AznDevil92
  • 554
  • 2
  • 10
  • 36

2 Answers2

1

Your were close:

SELECT movie_title, movie_year, movie_cost/price_rentfee As "Cost to price ratio"
FROM MOVIE
JOIN PRICE
ON movie.price_code = price.price_code
WHERE COALESCE(price_rentfee, 0) > 0;

If by any chance you made a typo and movie_cost should've been movie.cost and price_rentfee - price.rentfee then it would be like follows:

SELECT movie_title, movie_year, movie.cost/price.rentfee As "Cost to price ratio"
FROM MOVIE
JOIN PRICE
ON movie.price_code = price.price_code
WHERE COALESCE(price.rentfee, 0) > 0;
PM 77-1
  • 12,254
  • 20
  • 64
  • 106
  • What does COALESCE do? Can i replace that with ROUND because i want it to be 2 decimal places. – AznDevil92 Oct 22 '14 at 23:19
  • 1
    I do not know your schema. If `price` is `nullable` I needed protection. `COALESCE()` is a standard SQL function, that returns its first parameter that is not `null`. When used with just two parameters, it's like Oracle's `NVL()`. See [Oracle Differences between NVL and Coalesce](http://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce) post and its answers. – PM 77-1 Oct 22 '14 at 23:25
  • Yes, I simply implemented your "*has a price*" requirement. The same helped against division by zero. – PM 77-1 Oct 22 '14 at 23:39
0

Try this:

SELECT movie_title, movie_year, (movie_cost)/(price_rentfee) as 'cost'
FROM MOVIE
JOIN PRICE
ON movie.price_code = price.price_code;
Sam
  • 19,426
  • 2
  • 42
  • 67
Konrad
  • 36
  • 1