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

You're missing the GROUP BY

You can't mix aggregate expressions in the select list with non aggregated column references (in your case Theatres.Name) unless you group by those non aggregated columns.

Answer 2

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).

Answer 3

"SQL Command not properly ended" comes up when you have an inappropriate clause in the synax.. like you cannot have ORDER BY in a CREATE VIEW.

So if you have dont SELECT AVG(COL1),COL2 FROM ........ GROUP BY COL2 ORDER BY something Then I dont see why you getting this ORA exception.

Paste your last query which generated that exception and also let me know if you using it in a stored procedure , function or view.

Answer 4

Try this:

Select s.TheatreName Theatre, 
     Avg(m.rating) AvgRating  
From Movies m 
   left Join ShowAt s
     On s.movieTitle = m.Title
Group By s.TheatreName 
order By Avg(m.rating)

You don't need the movies table since you don;t need any columns from it