SQL Aggregation Function

Question

Okay so I have this code here:

SELECT MOVIETITLE AS "Movie Title", MIN(AVG(RATING)) AS "Lowest Average Rating"
FROM MOVIE, RATING
WHERE MOVIE.MOVIEID = RATING.MOVIEID
GROUP BY MOVIETITLE;

I need to fine the lowest average rating from my ratings table so I used the aggregation function

MIN(AVG(RATING))

I keep getting this error though and I can't figure out how to solve it:

ORA-00937: not a single-group group function

I am new to SQL and Oracle so this is all very new to me...

EDIT

Okay just to clarify things up, there are multiple people rating the same movies in the Ratings table and basically need to get the average of all of the ratings for each movie and list the movie with lowest average

Answer 1

And another one SQL Fiddle

select min(rating)
from (select m.movietitle, avg(r.rating) as rating
      from movie m, rating r
      where m.movieid = r.movieid
      group by m.movietitle) t;

Answer 2

you can't do that, try adding it in a subquery

SELECT MOVIETITLE AS "Movie Title", AVG(RATING) AS "AVGRating"
FROM MOVIE, RATING
WHERE MOVIE.MOVIEID = RATING.MOVIEID
GROUP BY MOVIETITLE
HAVING AVG(RATING) =
(
    SELECT MIN(AVG(RATING)) AS "AVGRating"
    FROM MOVIE, RATING
    WHERE MOVIE.MOVIEID = RATING.MOVIEID
    GROUP BY MOVIETITLE
)

• SQLFiddle Demo (simple example)