CODEWITHSUNDEEP
mysql
Psyche
Psyche

Reputation: 8783

Help with a SQL query

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?

Upvotes: 0

Views: 112

Answers (5)

Rick J
Rick J

Reputation: 2703

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

Upvotes: 4

sh_
sh_

Reputation: 73

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

Upvotes: 0

Adrian Grigore
Adrian Grigore

Reputation: 33318

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

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103585

Something like:

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

Upvotes: 2

David M
David M

Reputation: 72930

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

Upvotes: 3

Related Questions