CODEWITHSUNDEEP
sqloracle-databasefunctionaggregation
bloodstorm17
bloodstorm17

Reputation: 521

SQL Aggregation Function

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

Upvotes: 4

Views: 639

Answers (6)

Craig
Craig

Reputation: 5830

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;

Upvotes: 0

gordy
gordy

Reputation: 9806

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

Upvotes: 2

Nick Krasnov
Nick Krasnov

Reputation: 27261

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

Upvotes: 2

David Aldridge
David Aldridge

Reputation: 52376

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;

Upvotes: 0

John Woo
John Woo

Reputation: 263893

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
)

Upvotes: 2

Olaf Dietsche
Olaf Dietsche

Reputation: 74098

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;

Upvotes: 3

Related Questions