CODEWITHSUNDEEP
sqloracle-database
user3591952
user3591952

Reputation: 51

How can I use unidentified character in Where Clause

I have query in which I calculate profit by subtracting the Distribuion Cost from the Rental Amount. Then divide this result by the distribution cost. Convert this value to a percentage by multiplying by 100 then name the heading as "Profit %".

How can I produce a report of all movies that produce less than 100% profit. Include the name of the movie, the percentage profit returned (rounded to 1 decimal place; title Profit %) and the number of times it was rented (title Rented). My work is

SELECT Name,TO_CHAR(((Movie.RentalAmount - 
    DistributionCost)/DistributionCost)*100.00,'999.999') AS "Profit %",
    COUNT(MovieRented.MOVIEID)
FROM Movie , MovieRented
WHERE Movie.MovieID = MovieRented.MovieID
AND 'Profit %' <= '100.00';

Upvotes: 0

Views: 151

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

First of all: when you want one result row per movie, then group by movie.

Then look at 'Profit %' <= '100.00'. You are comparing two strings here. It should be the alias "Profit %" with double quotes instead, and still you would compare strings, where '20' > '100.00', because '2' > '1'.

Then if you want to find profits less than 100, you should use < and not <= of course.

BTW: Please use proper joins. Comma-separated joins where replaced by explicit joins for a reason.

select name, to_char(profitrate, '990D0') as "Profit %", rented
from
(
  select 
    m.name,
    ((sum(m.rentalamount) - sum(mr.distributioncost)) / sum(mr.distributioncost)) * 100.0 
      as profitrate,
    count(*) as rented
  from movie m
  join movierented mr on mr.movieid = m.movieid
  group by m.movieid, m.name
)
where profitrate < 100;

Upvotes: 1

hemalp108
hemalp108

Reputation: 1249

I believe as you are using aggregate function in the select query you need to use GROUP BY keyword in your query. You can use it as an inline view and you can use your filter condition over the result as below.

SELECT *
 FROM (
       SELECT Name,
              TO_CHAR(((Movie.RentalAmount -
              DistributionCost)/DistributionCost)*100.00,'999.999') AS "Profit %",
              COUNT(MovieRented.MOVIEID)
       FROM   Movie , 
              MovieRented
       WHERE  Movie.MovieID = MovieRented.MovieID
       GROUP BY  Name,
                 Movie.RentalAmount,
                 DistributionCost)
WHERE  TO_NUMBER("Profit %") <= 100;
    `

Upvotes: 0

pablomatico
pablomatico

Reputation: 2242

You just have to calculate again the profit in the where clause.

SELECT Name,TO_CHAR(((Movie.RentalAmount - 
    DistributionCost)/DistributionCost)*100.00,'999.999') AS "Profit %",
    COUNT(MovieRented.MOVIEID)
FROM Movie , MovieRented
WHERE Movie.MovieID = MovieRented.MovieID
AND (Movie.RentalAmount - DistributionCost)/DistributionCost <= 100;

Upvotes: 1

Related Questions