1

I have this code and it gives me sold seats summarized for each movie:

SELECT mName, SUM(soldSeats)
FROM movie, show, prog
WHERE movie.movieID = prog.movieID
AND prog.showID = show.showID
GROUP BY mName

The problem is to find movie that have the max of seats sold, I have tried to add this, but only get no rows:

HAVING SUM(soldseats) = (SELECT MAX(SUM(soldseats)) FROM show
GROUP BY solgteplasser)

Do anyone have a suggestion? Here is how the tables looks: Sum of a activity

Community
  • 1
  • 1
user1823846
  • 15
  • 3

1 Answers1

2
Select test.* 
from 
(Select movie.mName, SUM(show.soldSeats) as soldseatsum
FROM movie, show, prog
WHere movie.movieId = prog.movieID
AND prog.showID = show.showID
Group by movie.mName
Order by soldseatsum DESC) test
Where rownum<=1

This way you order your selection descending by your SUM and you select the first row only using Rownum which is the row with the highest SUm.

EDIT: Also in case you got Nulls in your SUM column make sure you add a NULLS LAST after your ORder By like this:

.....
.....
Group by movie.mName
    Order by soldseatsum DESC NULLS LAST) test
    Where rownum<=1
xray1986
  • 1,148
  • 3
  • 9
  • 28
  • Many thanks for your answer, your code worked perfectly. What do "test.*" exactly do? I have not seen this yet, is it some kind of a view? – user1823846 Nov 15 '12 at 14:14
  • Think of your initial Selection as a temporary table called test I use it to select all of its records that have a rownumber <=1 (which is essentialy the first row only). So Test is just an alias I gave to that selection. It 's not needed so much here since we only select fields from that particular selection. But I use it just to develop good habbits :) – xray1986 Nov 15 '12 at 14:26