Help with a SQL query

Question

Hello,

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?

Answer 1

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

Answer 2

Something like:

select top 10 song_album
from songs
group by song_album
order by sum(song_views) desc

Answer 3

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

Answer 4

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...

Answer 5

SELECT TOP 10 song_album FROM songs GROUP BY song_album ORDER BY sum(song_views) desc