Understanding summary of inner query

Question

Problem understanding subquery

I do not understand this example from www.sqlitetutorial.net/sqlite-subquery :

Only one number is returned by the inner query: 1422138358

But the average of this number is different:

So why is the average of 1422138358 not 1422138358? The two queries are not independent? If I remove "ORDER BY albumid" the result is the same:

Example data:
http://www.sqlitetutorial.net/sqlite-sample-database/

Edit: Ok, there is probably some integer overflow going on as the columns are integer, but I still don't understand why the example take the average of a single number?

Answer 1

Very possibly that it was a mistake

1) From the text you can see that they wanted to 'sum the size of an album' and you`re querying Tracks table, which supposedly have an album_ID column

2) You cannot use ORDER BY if you`re using only aggregation column such as

select SUM(bytes)
from Tracks
Order by albumID

because it has nothing to order it by from.

Also note that you cannot use order by in subqueries

Finally what was missing here was this remaining of the query :

Select AVG(album.size) as [avg(album.size)]
from (
select albumID,SUM(bytes) as size
from Tracks
GROUP BY albumID

) as album

You can learn more about subqueries here

And if you want to play around with these, heres the code that you can replicate and use it for further exercies on that website:

    CREATE TABLE tracks (AlbumID int,bytes int)
    CREATE TABLE albums (AlbumID int, title nvarchar(50))
    insert into Tracks values (1,2),(2,10),(3,15)
    Select AVG(album.size) as [avg(album.size)]
    from (
    select AlbumID,SUM(bytes) as size
    from tracks
    GROUP BY albumID

) as album

Hope it helps