CODEWITHSUNDEEP
sqloracle-database
nogear
nogear

Reputation: 1

List the production company info for the movie with highest opening weekend Gross /Budget ratio

I have two tables


ProdCos
ProdID ProdName ProdAddress ProdCity ST ProdZIP ProdPhone

AND

Movies
MovID MovTitle Genre RelDate DirLName DirFName Rate Budget OpWkEnd Rtime ProdID

I need to list the production company (ProdNamd) for the movie (ProdID) with the highest opening weekend Gross/Budget ratio.

So far I have:

    SELECT p.ProdName, p.ProdID, m.ProdID, MAX(SELECT SUM(m.OpWkEnd / m.Budget) AS "Ratio" )
    FROM Movies m, ProdCos p
    WHERE p.ProdID = m.ProdID
    GROUP BY p.Prodname, m.ProdID, p.ProdID;

It returns:

ORA-00936: missing expression

Upvotes: 0

Views: 91

Answers (3)

nogear
nogear

Reputation: 1

I was able to use:

SELECT p.ProdName, p.ProdID, m.ProdID, (SUM(m.OpWkEnd) /SUM(m.Budget)) AS "Ratio" FROM Movies m, ProdCos p WHERE p.ProdID = m.ProdID AND ROWNUM=1 GROUP BY p.ProdName, p.ProdID, m.ProdID ORDER BY (SUM(m.OpWkEnd) /SUM(m.Budget)) DESC;

Which returned:

PRODNAME PR PR Ratio

Atlas Entertainment 10 10 .692962893

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

The straight-forward way is this: find the movie(s) with the highest ratio. Then find the related production company / companies. As of Oracle 12c this is simply:

select *
from prodcos
where prodid in
(
  select prodid
  from movies
  order by opwkend / budget desc
  fetch first 1 row with ties
)

In earlier versions you can use RANK or DENSE_RANK to rank your movies instead:

select *
from prodcos
where (prodid, 1) in
(
  select prodid, rank() over (order by opwkend / budget desc) as rnk
  from movies
);

Upvotes: 1

RToyo
RToyo

Reputation: 2877

The error is about this expression in your select statement:

MAX(SELECT SUM(m.OpWkEnd / m.Budget) AS "Ratio" )

You're not specifying which table to SELECT from; also, the sum is an agrigate function, so you wouldn't need to select its max, since you're not grouping that particular select statement.

However, you don't need to even SELECT anything, since you're pulling data from the tables already. You can just select the sum of those two fields directly.

See this query; I've only adjusted that one Ratio column in your selection:

SELECT 
    p.ProdName, p.ProdID, m.ProdID, 
    (SUM(m.OpWkEnd) / SUM(m.Budget)) AS "Ratio"
FROM 
    Movies m, ProdCos p 
WHERE
    p.ProdID = m.ProdID 
GROUP BY 
    p.Prodname, m.ProdID, p.ProdID

Upvotes: 2

Related Questions