CODEWITHSUNDEEP
pythondatabase
liteversion
liteversion

Reputation: 69

python database query chinook

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

Upvotes: 0

Views: 1265

Answers (1)

D. Foley
D. Foley

Reputation: 1079

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

Upvotes: 1

Related Questions