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
  • 11
  • 39
  • 1
    `WHERE x/y` -- missing an operator after that, (i.e. `=`, `<`, etc.) Or do you just need to move your `where` criteria to your `select` clause and remove the `where` clause? A little unclear. – sgeddes Oct 22 '14 at 22:47
  • It's a bit strange that you keep price in a separate table. What's the reason for that? – Multisync Oct 22 '14 at 22:56
  • The price table is its own because in the table there is a PRICE_RENTFEE and PRICE_DAILYLATEFEE – AznDevil92 Oct 22 '14 at 23:24

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,933
  • 21
  • 68
  • 111
  • 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
  • 20,096
  • 2
  • 45
  • 71
Konrad
  • 36
  • 1