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

What do you mean by "lowest average rating"? I can see how a movie can have a lowest rating, and an average rating, but those are two different things. — Brian Willis
– Brian Willis, Commented Nov 19, 2012 at 8:24
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 — bloodstorm17
– bloodstorm17, Commented Nov 19, 2012 at 8:28

Olaf Dietsche · Accepted Answer · 2012-11-19 08:36:58Z

3

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;

edited Nov 19, 2012 at 8:36

answered Nov 19, 2012 at 8:31

Olaf Dietsche

74.4k9 gold badges113 silver badges214 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Aldridge Over a year ago

That's probably the best if the name of the movie is not also required.

John Woo · Accepted Answer · 2012-11-19 09:15:17Z

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)

edited Nov 19, 2012 at 9:15

answered Nov 19, 2012 at 8:25

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

Nick Krasnov · Accepted Answer · 2012-11-19 12:12:49Z

Another approach(If there are several movies with the same minimum rating, they all 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

gordy · Accepted Answer · 2012-11-19 19:30:05Z

2

It would be nice if there were a standard way to include additional values with aggregates. I find myself combining many values into a single RAW value, taking the aggregate of that 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 an int and rating is a number (see the SQL Fiddle DDL). If both are ints or numbers you can also 'pack' them by shifting the more significant value to the left (multiply it by a power of 2) and adding them together.

SQL Fiddle

edited Nov 19, 2012 at 19:30

answered Nov 19, 2012 at 9:10

gordy

9,9713 gold badges37 silver badges49 bronze badges

4 Comments

Kirill Leontev Over a year ago

this is interesting. How does it perform on massive amounts of data?

gordy Over a year ago

@beherenow I arrived at this solution having to do a similar thing for massive amounts of data. If you compare the execution plans you can see it's more efficient than the other solutions here as rating is scanned once and a single row is retrieved from movie.

David Aldridge Over a year ago

I admire the ingenuity of the approach, but I believe that one of the qualities of good code is that it be easily understandable, and I don't think that it passes that test.

gordy Over a year ago

@DavidAldridge totally agree and I think SQL could use some standard semantics for packing and unpacking for aggregation. Something like min(rating, movieid).movieid - even if you could just easily form a tuple would be nice. It's a tough decision to choose readability over efficiency when the performance penalty can be so severe. In this case I would opt for comments (I'll add some comments now..)

David Aldridge · Accepted Answer · 2012-11-19 11:00:51Z

0

Calculate the average rating, order-by ascending, and take 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;

answered Nov 19, 2012 at 11:00

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

2 Comments

Grisha Weintraub Over a year ago

The only one problem with this solution - if you have more than one title with the same lowest rating.

David Aldridge Over a year ago

Yes indeed ... probably not an issue in the context of a movie rating website, but more of a concern with something like "show me the lowest cost suppliers ..." or summit

Craig · Accepted Answer · 2013-11-14 18:52:54Z

0

If you need the movie title as well, I would do this using an Analytic function to get the min. This allows you to only hit each table once (where the solution given by 今草顿 웃 will hit each table twice.. once in the main select and once in the "having" select).

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;

answered Nov 14, 2013 at 18:52

Craig

5,87024 silver badges31 bronze badges

Collectives™ on Stack Overflow

SQL Aggregation Function

6 Answers 6

1 Comment

Comments

Comments

4 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related