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:
#connectionimport sqlite3try:db = sqlite3.connect('C:/Users/chinook.db')print('connection succesful')except:print('connection error')cur = db.cursor()query3 = '''SELECT t.Name, t.trackId, t.albumIdFROM tracks as tINNER JOIN invoice_items as iON t.trackId = i.trackIdINNER JOIN invoices AS iiON i.invoiceId = ii.invoiceIdORDER BY ii.invoiceId DESCLIMIT 10;'''I'm using SQlite to acces my databaseand the import module sqlite3can anyone please help? I'm terrible with databases...
Best Answer
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 albuminner join track on album.AlbumId = track.AlbumIdinner join invoiceline on track.TrackId = invoiceline.TrackIdinner join artist on album.ArtistId = artist.ArtistIdgroup by album.AlbumIdorder by AlbumBuyTotal desclimit 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..