The 
Query 1:
select tracks.albumid, sum(tracks.unitprice) as SumOfUnitPrice from tracks
group by tracks.albumid having tracks.albumid in
(select albums.albumid from albums where albums.artistid in
(select artists.ArtistId from artists where artists.Name = "Audioslave"
)
)
Result 1:
| AlbumId | SumOfUnitPrice |
| 10 | 13.86 |
| 11 | 11.88 |
| 271 | 13.86 |
Query 2:
select albums.albumid,
(select sum(tracks.unitprice) from tracks
group by tracks.albumid having tracks.albumid in
(select albums.albumid from albums where albums.artistid in
(select artists.ArtistId from artists where artists.Name = "Audioslave"
)
)
) as SumOfUnitPrice
from albums where albums.artistid in
(select artists.ArtistId from artists where artists.Name = "Audioslave"
)
Result 2:
| AlbumId | SumOfUnitPrice |
| 10 | 13.86 |
| 11 | 13.86 |
| 271 | 13.86 |
CodePudding user response：
The problem is, that your subquery within the select block of Query 2 has no relation to the outer one. SQLite will limit the result of the subquery to the first record, which therefore always returns 13.86. In other RDBMS this will cause an exception, that your subquery returns more than one row.
This should provide the same result as Query 1:
select a.albumid,
( select sum(t.unitprice)
from tracks t
where t.albumid = a.albumid
group by t.albumid
) as SumOfUnitPrice
from albums a where a.artistid in
(select artists.ArtistId from artists where artists.Name = "Audioslave")
A much better solution would be to employ joins:
select t.albumid
sum(t.unitprice) as sum_of_unitprice
from tracks t
join albums a on a.albumid = t.albumid
join artists ar on ar.artistid = a.artistid
where ar.name = "Audioslave"
group by t.albumid
CodePudding user response：
Your first query is correct in second query your getting query gives you error due to sub query returns multiple records.
You just need to add where clause in second query as below to get same result.
Anyhow best practice of query with joins. as provided by Dominik Klug
select Outer_albums.albumid,
(select sum(tracks.unitprice) from tracks
where tracks.albumid = Outer_albums.albumid
having tracks.albumid in
(select albums.albumid from albums where albums.artistid in
(select artists.ArtistId from artists where artists.Name = "Audioslave"
)
)
) as SumOfUnitPrice
from albums Outer_albums
where Outer_albums.artistid in
(select artists.ArtistId from artists where artists.Name = "Audioslave"
)
