Someone

select file_type,rel,count(song) AS num_songs,band,rel_name,song_name,genre,  (SELECT max(rating) AS rating FROM    (select round(sum(rating)/count(rating)) AS rating FROM song_rating WHERE rel=t1.rel group by song)  AS t2) AS ratingfrom file NATURAL LEFT JOIN song AS t1 NATURAL LEFT JOIN relNATURAL LEFT JOIN song_rating group by rel;