CODEWITHSUNDEEP
sqlaggregateaverage
Shinji-san
Shinji-san

Reputation: 1001

SQL nested aggregate function AVG with Join statement

I have two tables, first table is called Movies, second table is called Reviews like so:

Table Movies: [ MovieID / MovieName / MovieYear] 

Table Reviews: [ReviewID / MovieID / userID / Rating / ReviewDate]

where columns are separated by the /

I am trying to perform a query that will join both tables outputting MovieID, MovieName, MovieYear, number of reviews and Average Rating, and I got the query right until I got to the average rating part. I am getting an error in Azure Studio:

"Cannot perform an aggregate function on an expression containing an aggregate or a subquery. "

And my confusion is that with the average function it's supposed to compute the average for a single movie but I also didn't want to do two queries where with the first query I just leave out the average bit, get the rating for each and then in the second query then compute the average; I was wondering if there was a way to do this all in one query. Below is my query I have so far:

SELECT 
  Movies.MovieID, 
  Movies.MovieName,
  COUNT(Reviews.MovieID) AS NumReviews, 
  AVG(CONVERT(float, COUNT(Rating) )) AS  AvgRating 
FROM 
  Movies 
  JOIN Reviews ON(Reviews.MovieID = Movies.MovieID)
GROUP BY 
  Movies.MovieID, 
  Movies.MovieName

Upvotes: 0

Views: 1145

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Is this what you want?

SELECT m.MovieID, m.MovieName,
       COUNT(*) AS NumReviews, 
       AVG(CONVERT(float, r.Rating)) AS AvgRating 
FROM Movies m JOIN
     Reviews r
     ON r.MovieID = m.MovieID
GROUP BY m.MovieID, m.MovieName;

This calculates the average rating, which seems like the most reasonable calculation for ratings.

Upvotes: 2

dwir182
dwir182

Reputation: 1549

First you need to explicit your COUNT(RATING) Because Error says really clear.. Aggregate cannot contain other aggregate expression in it..

SELECT 
  MovieID, 
  MovieName,
  NumReviews, 
  AVG(CONVERT(float, CountRating)) AS AvgRating 
FROM 
 (SELECT 
    Movies.MovieID, 
    Movies.MovieName, 
    Count(Reviews.MovieID) as NumReviews
    Count(Reviews.Rating) as CountRating
  FROM
    Movies
    INNER JOIN Reviews ON (Reviews.MovieID = Movies.MovieID)
  GROUP BY
    Movies.MovieID,
    Movies.MovieName) As Derived_Table

Upvotes: 1

gordy
gordy

Reputation: 9786

is there a reason you wrote it as AVG(COUNT(Rating))? if you want the average rating just use AVG(Rating)

Upvotes: 0

Related Questions