How to do arithemtic operations with an alias in sqlite

Question

I want to calculate with an alias in sqlite (Example is modified from http://www.sqlitetutorial.net):

if i do it like this, i get the error message "no such column: tracks_count"

SELECT albumid,
       title,
       (
           SELECT count(trackid) 
             FROM tracks
            WHERE tracks.AlbumId = albums.AlbumId
       )
       tracks_count, tracks_count * album_nr
  FROM albums
 ORDER BY tracks_count DESC;

if i do it like this, i get zero for the mulitplication

SELECT albumid,
       title,
       (
           SELECT count(trackid) 
             FROM tracks
            WHERE tracks.AlbumId = albums.AlbumId
       )
       tracks_count, "tracks_count" * album_nr
  FROM albums
 ORDER BY tracks_count DESC;

Table data for the example:

table albums

table tracks

Answer 1

You don't even need a subquery here:

SELECT
    a.albumid,
    a.title,
    COUNT(t.albumid) AS tracks_count,
    COUNT(t.albumid) * a.album_nr AS other_count
FROM albums a
LEFT JOIN tracks t
    ON a.albumid = t.albumid
GROUP BY
    a.albumid,
    a.title;

If you wanted to make your current approach work, then the problem you are having is that you are referring to the tracks_count alias in the same select in which it was defined. This isn't allowed, because the alias may not have even been computed yet. But, I would recommend using the answer I gave above.