CODEWITHSUNDEEP
postgresql
Anon Li
Anon Li

Reputation: 621

Calculating AVG; output those higher than the average

Find all live albums that were released by artists from CA (Canada) and that have a higher rating than the average rating of all albums (of any type, by any artist) released in the same year. For each album satisfying the requirements, output its title, artist’s name, and year (in this order).

Upvotes: 0

Views: 71

Answers (1)

Yun Luo
Yun Luo

Reputation: 1676

use window function to do this:

select t.album, t.name artist, t.year 
  from (
    select b.name album, a.name, b.year, b.ratings, avg(b.ratings) over (partition by b.year) year_rat
      from albums b, artists a, countries c
     where a.country=c.code
       and c.code='CA1'
       and a.name=b.artist
 ) t
 where t.ratings >= t.year_rat

Or, if using no window functions:

select albums.title, artists.name, albums.year
  from artists 
  join albums on albums.artist= artists.name
  join (select year, avg(ratings) avg_ratings from albums group by year) As temp 
    on temp.year=albums.year
 where artists.country='CA1'
   and albums.ratings >= temp.avg_ratings

Upvotes: 1

Related Questions