SQL aggregation function

Question

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 rate the lowest average rating from my rating table, so I used the aggregation function

MIN(AVG(RATING))

I keep getting this error, although 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

Well, just to clarify the situation, there are several people who rate the same movies in the Ratings table and basically need to get the average of all ratings for each movie and list the movie with the lowest average

Answer 1

And another 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 cannot do this, try adding it to 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)

Answer 3

Another approach (if there are several films with the same minimum rating, all of them will be displayed):

 -- sample of data just for the sake of demonstration SQL> with movie as( 2 select 1 as movieid , 'Departed' as movietitle from dual union all 3 select 2, 'Shutter Island' from dual union all 4 select 3, 'Terminator' from dual 5 ), 6 rating as( 7 select 1 as movieid, 7 as rating from dual union all 8 select 1, 8 from dual union all 9 select 1, 9 from dual union all 10 select 1, 6 from dual union all 11 select 1, 7 from dual union all 12 select 2, 9 from dual union all 13 select 2, 5 from dual union all 14 select 2, 6 from dual union all 15 select 3, 6 from dual union all 16 select 3, 5 from dual union all 17 select 3, 6 from dual 18 ) -- the query 19 select w.movietitle as "Movie Title" 20 , round(w.mavr, 1) as "Lowest Average Rating" 21 from ( select movietitle 22 , min(avg(rating)) over() as mavr 23 , avg(rating) as avr 24 from movie 25 , rating 26 where movie.movieid = rating.movieid 27 group by movietitle 28 ) w 29 where w.mavr = w.avr 30 ; 

Result:

 Movie Title Lowest Average Rating -------------- --------------------- Terminator 5,7 

Answer 4

It would be nice if there was a standard way to include additional values ​​in aggregates. I find that I combine many values ​​into a single RAW value, taking the totality of this, and then extracting the original values ​​from the aggregation:

 /* lowest returns a single row */ with lowest as ( select min( /* combine movieid and avg(rating) into a single raw * binary value with avg(rating) first so that min(..) * will sort by rating then by movieid */ utl_raw.overlay( utl_raw.cast_from_binary_integer(movieid), utl_raw.cast_from_number(avg(rating)), 5)) packed from rating group by movieid) /* extract our rating and movieid from the packed aggregation * and use it to lookup our movietitle */ select movietitle, utl_raw.cast_to_number(utl_raw.substr(packed,1,3)) rating from movie m, lowest l where m.movieid= utl_raw.cast_to_binary_integer(utl_raw.substr(packed,5,4)) 

note: this assumes movieid is int and rating is a number (see SQL Fiddle DDL). If both are int or numbers, you can also “pack” them by moving the more significant value to the left (multiply by 2) and add them together.

SQL Fiddle

Answer 5

Calculate the average rating, ascending order and make the first result.

  SELECT * FROM ( SELECT MOVIETITLE AS "Movie Title", AVG(RATING) AS "Lowest Average Rating" FROM MOVIE, RATING WHERE MOVIE.MOVIEID = RATING.MOVIEID GROUP BY MOVIETITLE ORDER BY 2 ASC) WHERE ROWNUM = 1; 

Answer 6

If you need a movie title, I would do it using the Analytic function to get the min. This allows you to hit each table only once (when the solution given by 今 草 顿 웃 falls into each table twice .. once in the main choice and once in the “have” choice).

 select movietitle as "Movie Title", avgrating as "Lowest Average Rating" from ( select m.movietitle, avg(r.rating) avgrating, rank() over (order by avg(rating)) rank from movie m inner join rating r on r.movieid = m.movieid group by m.movietitle ) where rank = 1;