Vfrdtyky

I have a table
Films

id  release_year    category_id rating

1   2015                 1         8

2   2015                 2        8.5

3   2015                 3         9

4   2016                 2        8.2

5   2016                 1        8.4

6   2017                 2         7

I want to add a new column "avg_better_films" to find an average rating of all better films in its release year

the output should be

id  release_year    category_id rating  avg_better_films

1   2015                1         8          8.75

2   2015                2        8.5           9

3   2015                3         9        Not Available

4   2016                2        8.2          8.4

5   2016                1        8.4       Not Available

6   2017                2         7        Not Available

and you can see, when the film has the best rating in the release year
it will show "Not Available"

Do you know how to get this output in MySQL

• We can use a Correlated Subquery to calculate the Average rating for the "better films" for the same year.


• Subquery would return null in case of no better films for a specific film and year.


• We can then use Coalesce() function to handle the case when there is no "better film" found for the year.


• Thanks to @Strawberry in comments, we need to do + 0 to the result of subquery, so that MySQL considers it as number

Try the following:

SELECT

  t1.id, 

  t1.release_year, 

  t1.category_id, 

  t1.rating, 

  COALESCE(

           (

            SELECT AVG(t2.rating) 

            FROM Films AS t2

            WHERE t2.rating > t1.rating AND  -- higher rating films

                  t2.release_year = t1.release_year -- films from same year

           ) + 0, 

           'Not Available' -- handle null result of subquery

          ) AS avg_better_films

FROM Films AS t1

SQL Fiddle: http://sqlfiddle.com/#!9/4960ea/3

you can try using scalar subquery

select *, (select avg(rating) from Films b on a.release_year=b.release_year) as avg_film_Rating

from Films a

This is tricky, because you need to output the results as a string, not a number. If any value is a string, all must be.

I would recommend a correlated subquery that looks like:

SELECT f.*,

       (SELECT COALESCE(FORMAT(AVG(t2.rating), 2), 'Not Available)

            FROM Films f2

            WHERE f2.rating > f.rating AND 

                  f2.release_year = f.release_year

       ) AS avg_better_films

FROM Films f

Notes:

• Table aliases should be abbreviations for the table name.


• If no rows match the WHERE clause in the subquery, then AVG() returns NULL. Hence, the COALESCE()can be in the subquery.


• 
  FORMAT() is used to convert the average rating to a string.