python database query chinook

Question

I've recently started a python assignment which uses the chinook database. The assignment that I'm stuck on is figuring out which album is listened to most. Then, I need to write print the top 10 results with the name of the album and the artist, and the number of times a track is played on the album. Also, i need to fit all this into one query. I honestly have no idea how to do this and stackOverflow is about my last resort. here's my horrible attempt:

#connection
import sqlite3
try:
 db = sqlite3.connect('C:/Users/chinook.db')
 print('connection succesful')
except:
 print('connection error')
cur = db.cursor()
query3 = '''
SELECT t.Name, t.trackId, t.albumId
FROM tracks as t
INNER JOIN invoice_items as i
ON t.trackId = i.trackId
INNER JOIN invoices AS ii
ON i.invoiceId = ii.invoiceId
ORDER BY ii.invoiceId DESC
LIMIT 10
;'''

I'm using SQlite to acces my database and the import module sqlite3 can anyone please help? I'm terrible with databases...

• MohammadMahdi Eilbeigi: Your question is about SQL and how to query on it. So you need to provide table schemas that people can help you.
• D. Foley: If it helps, I think this might be the database. github.com/lerocha/chinook-database
• D. Foley: Have you resolved your issue?
• D. Foley: Do you mean which album has the most purchases? I assume that's what you mean, given that you're joining onto invoices. Looking at the current schema for chinook I can't see anything related to "number of times listened to".

Answer 1

Assuming you meant to get total album sales, not times listened to, I think this query does the trick.

select album.AlbumId, album.Title, artist.Name, count(album.AlbumId) as AlbumBuyTotal from album
inner join track on album.AlbumId = track.AlbumId
inner join invoiceline on track.TrackId = invoiceline.TrackId
inner join artist on album.ArtistId = artist.ArtistId
group by album.AlbumId
order by AlbumBuyTotal desc
limit 10;

Have a look at the group by statement, as that is key in separating purchased album totals into a respective row.

Same concept can apply with "times listened to" but I can't find any information like that in the database..

• liteversion: Thank you, you're a genius!