1

[https://www.sqlitetutorial.net/sqlite-sample-database/]

I wrote this code to get the answer to 'What is the total price for the album “Big Ones”?'

SELECT  al.Title, i.Total, tr.albumid, SUM(tr.UnitPrice)
FROM ((((artists ar INNER JOIN albums al ON ar.ArtistId = al.ArtistId)
INNER JOIN tracks tr ON al.AlbumId = tr.AlbumId)
INNER JOIN invoice_items ii ON tr.TrackId = ii.TrackId)
INNER JOIN invoices i ON ii.invoiceid = i.invoiceid)
WHERE al.Title = 'Big Ones'

I got the SUM(tr.UnitPrice) as 9.9.

But 9.9 is wrong. What should be the correct answer?

Tables from Chinook database is used. The link is provided above.

forpas
  • 160,666
  • 10
  • 38
  • 76
Priyanshu Kumar Jha
  • 15
  • 1
  • 4

2 Answers2

1

You must sum the unit prices of all the tracks of the album, so only 2 tables are needed: albums and tracks.
Join them, and aggregate:

select a.title, 
       sum(t.unitprice) total_price
from albums a inner join tracks t 
on t.albumid = a.albumid
where a.title = 'Big Ones'
group by a.title

You can get the same result even if you omit the group by clause.

forpas
  • 160,666
  • 10
  • 38
  • 76
-1
select
Title,
sum(UnitPrice)
from       albums
join       tracks
on         albums.AlbumId = tracks.AlbumId
where      albums.Title = 'Big Ones'
Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
Maddy Pnr
  • 1