Follow-Up Help with a SQL Query

Question

I asked something similar a few days ago, here is my issue. My professor has phrased the following question: Find the average ratings of all movies playing at each theatre. Display the theatre name and the computed rating. Order the results ascending by rating.

Here is how my tables are structured:

CREATE TABLE Theatres (
  Name varchar2(50) not null,
  City varchar2(50) not null,
  State varchar2(50) not null,
  Zip number not null,
  Phone varchar2(50) not null,
  PRIMARY KEY (Name)
);

CREATE TABLE Movies (
 Title varchar2(100) not null,
 Rating NUMBER not null,
 Length NUMBER not null,
 ReleaseDate date not null,
 PRIMARY KEY (Title),
 CHECK (Rating BETWEEN 0 AND 10),
 CHECK (Length > 0),
 CHECK (ReleaseDate > to_date('1/January/1900', 'DD/MONTH/YYYY'))
);

CREATE TABLE ShownAt (
  TheatreName varchar2(50) not null,
  MovieTitle varchar2(100) not null,
 PRIMARY KEY (TheatreName, MovieTitle),
  FOREIGN KEY (TheatreName) REFERENCES Theatres(Name),
  FOREIGN KEY (MovieTitle) REFERENCES Movies(Title)
);

SELECT 
 AVG(Movies.Rating), 
 Theatres.Name
 FROM Theatres
 JOIN ShownAt ON ShownAt.TheatreName = Theatres.Name
 JOIN Movies ON ShownAt.MovieTitle = Movies.Title
 ORDER BY Movies.Rating ASC

See anything out of the ordinary? I keep getting an error stating SQL Error: ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function". Forgive my beginner-ness.

Answer 1

Use:

  SELECT AVG(m.rating) AS avg_rating, 
         t.name
    FROM THEATRES t
    JOIN SHOWNAT sa ON sa.theatrename = t.name
    JOIN MOVIES m ON m.title = sa.movietitle
GROUP BY t.name
ORDER BY avg_rating

The GROUP BY must include all the columns that are not referenced inside of aggregate functions (IE: MAX, MIN, AVG, COUNT, etc).