[MySQL] How to find the difference between the average rating of films released before 1980 and the average rating of films released after 1980?

Question

MariaDB

you need to first calculate the average rating yes 1980, then after 1980 and get their difference, I can’t do all this in one query, I already tried to do it with nested queries, but I don’t understand how to compare these values:
MariaDB [rating]> select avg (c. avg_stars) from (select avg(stars) avg_stars from Movie join Rating using(mID) where year>1980 group by mID having avg(stars)) c;
only for the first case... but I don't understand how to fit all this into one query... I just started learning sql...

spoiler

Answer 1

1. No need to think that 1 request instead of 2 is a priori good.
As a rule, nested queries work quite badly, and if there are no mega significant arguments, it is better to make 2 queries.
Plus, there is my personal point of view - you should not remove business logic in sql queries.
It is much clearer to make a function in your favorite programming language that will calculate the average rating for 2 arguments (start year, end year) - and calculate the difference already in the script.
2. if you really want to

select 	
    (
      select avg(Rating.stars)  
      from Movie 
      join Rating on Rating.mID = Movie.mID
      where year < 1980 
    ) - (
      select avg(Rating.stars)  
      from Movie 
      join Rating on Rating.mID = Movie.mID
      where year >= 1980 
    ) as diff;