Understanding the summary of an internal request

Question

Subquery issue

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

Only one number is returned by the internal query: 1422138358

But the average value of this number is different:

So why on average is 1422138358 not 1422138358? Are these two queries independent? If I remove the "ORDER BY albumid", the result will be the same:

Data examples:
http://www.sqlitetutorial.net/sqlite-sample-database/

Change Well, probably, an integer overflow occurs, since the columns are integer, but I still do not understand why the example takes an average value for a single number?

Answer 1

Maybe it was a mistake

1) From the text you can see that they wanted to “summarize the size of the album”, and you are querying the track table, which supposedly has the album_ID column

2) You cannot use ORDER BY if you only use an aggregation column such as

select SUM(bytes) from Tracks Order by albumID 

because he has nothing to order.

Also note that it cannot use order in subqueries

Finally, the missing query was missing here:

 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 with them, use the code that you can reproduce and use it for further exercises on this 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 this helps