I have to perform a SQL query to get the top 10 albums according to number of views. The number of views is actually the sum of the views for each song in the specified album.
My tables are:
albums:
- album_id
- album_name
- album_owner
songs:
- song_id
- song_name
- song_album
- song_owner
- song_views
Can you guys help me out with this one?
select sum(song_views) as 'song_sum',album_name
from albums a
inner join
songs s
on a.album_id = s.song_album
group by album_name
order by song_sum DESC
limit 0,10;
if song_album refers to album id...
Try this:
SELECT TOP 10 a.album_id, a.album_name, a.album_owner, SUM(s.song_views)
FROM albums a
INNER JOIN
songs s
ON a.album_id = s.song_album
GROUP BY a.album_id, a.album_name, a.album_owner
ORDER BY SUM(s.song_views) DESC
Something like:
select top 10 song_album
from songs
group by song_album
order by sum(song_views) desc
I could not run this, but it should be along the lines of
select album_name, sum(song_views) as views from albums join songs on songs.album_id = songs.song_album group by album_id order by views desc limit 10
SELECT TOP 10 song_album FROM songs GROUP BY song_album ORDER BY sum(song_views) desc
