-- 4. Get a listing of albums, ordered by their total ratings. The rating of an album is equal to the sum of the total ratings of the songs that it contains. In turn, the total rating of a song is equal to the sum of its ratings. (Use 4 for the highest possible individual rating and 1 for the lowest.)

SELECT soong.albumtitle AS "Album Title", SUM(songs.sum) AS "Total Album Rating"
FROM  Song soong, (
	(SELECT tsratings.songtitle, SUM(tsratings.thesong)
	FROM Song soong, (
		(Select SUM(1) AS thesong, sr1.songtitle
		FROM SongRating sr1, Song s1
		WHERE sr1.songrating='it sucks!' AND s1.songtitle=sr1.songtitle
		GROUP BY sr1.songtitle)
	UNION
		(Select SUM(2) AS thesong, sr1.songtitle
		FROM SongRating sr1, Song s1
		WHERE sr1.songrating='bad' AND s1.songtitle=sr1.songtitle
		GROUP BY sr1.songtitle)
	UNION
		(Select SUM(3) AS thesong, sr1.songtitle
		FROM SongRating sr1, Song s1
		WHERE sr1.songrating='good' AND s1.songtitle=sr1.songtitle
		GROUP BY sr1.songtitle)
	UNION
		(Select SUM(4) AS thesong, sr1.songtitle
		FROM SongRating sr1, Song s1
		WHERE sr1.songrating='amazing!' AND s1.songtitle=sr1.songtitle
		GROUP BY sr1.songtitle))AS tsratings
	WHERE tsratings.songtitle=soong.songtitle
	GROUP BY tsratings.songtitle
	ORDER BY SUM(tsratings.thesong) DESC)) AS songs
WHERE songs.songtitle=soong.songtitle 
GROUP BY soong.albumtitle
ORDER BY SUM(songs.sum) DESC;