CODEWITHSUNDEEP
sqlaverage
Emil Andreasson
Emil Andreasson

Reputation: 11

SQL Average of average

Hi I am struggling with the following exercise:

Find the difference between the average rating of articles released before 1950 and the average rating of articles released after 1950. (Make sure to calculate the average rating for each article, then the average of those averages for articles before 1950 and articles after. Don't just calculate the overall average rating before and after 1950.) 

SELECT AVG(RatingBefore + RatingAfter) AS Difference FROM Rating
WHERE AVG(star) AND year >1950 AS AverageAfter
AND AVG(star) AND year<1950 AS AverageAfter
GROUP BY title

Is this code something that would work? I am new to SQL so please be nice if it is a mess. :) (It is not specified what type of SQL in the exercise I have been given.)

Upvotes: 1

Views: 1409

Answers (1)

gbn
gbn

Reputation: 432431

This is a guess because we have no other info (sampel data, table structure, etc)

SELECT
  AVG(CASE year >1950 THEN star END) - AVG(CASE year < 1950 THEN star END),
  AVG(star)
FROM Rating
GROUP BY title

This is conditional aggregation to work out the before 1950 and after 1950 averages in the same query as the overall average.

Upvotes: 2

Related Questions