CODEWITHSUNDEEP
sql
Carson Cunningham
Carson Cunningham

Reputation: 11

How to Average an aggregate column

How do I write a query that averages an aggregate (another average)?

Here's what I have so far.

SELECT AVG(stars)
FROM Rating
WHERE mID IN
    (SELECT mID
    FROM Movie
    WHERE year > 1980)
GROUP BY mID

I want to average the output of that query.

The underlying data is: 

(Stars,mID)

(2,104)
(3,104)
(3,107)
(5,107)
(4,108)
(2,108)
(4,108)

I can get averages grouped by mID with the above query.

[AVG(stars),mID]

(2.50,104)
(4.00,107)
(3.33,108)

Now I want to take the avg of (2.5 + 4.0 + 3.33)

Expected answer = 3.2767

Upvotes: 0

Views: 427

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use the first resul as a FROM( subquery ) for the main avg 

select avg(my_avg)
from  (
  SELECT AVG(stars) my_avg 
  FROM Rating r 
  INNER JOIN (
      SELECT mID
      FROM Movie
      WHERE year > 1980 
  ) t on t.mID  = r.mID
  GROUP BY mID
) t2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can get an overall average of all the ratings as:

SELECT AVG(stars)
FROM Rating
WHERE mID IN (SELECT mID FROM Movie WHERE year > 1980);

But you probably want each movie to have the same weight. For that us a subquery:

SELECT AVG(avg_stars)
FROM (SELECT AVG(stars) as avg_stars
      FROM Rating
      WHERE mID IN (SELECT mID FROM Movie WHERE year > 1980);
      GROUP BY mID
     ) r;

Upvotes: 1

Related Questions