SQLite Forum

Help needed to return the right math results in sql query
> ... Do I need another sub-select or can it be within the existing one?

In the spirit of gifting a fishing pole rather than fish:

A set of select "projection"<sup>1</sup> expressions may use identifiers that are in scope for that select construct. The aliases you assign are not suddenly in that scope. Rather, they name the elements of output tuples from that select construct.

So to answer your question yourself: Identify the select constructs, which may be nested within other select constructs or in DML where tuple set can be used to qualify operations or provide data for them. Then, for each select construct, ask: Do the projection expressions use column names exposed by the table(s) or view(s) or subselect(s) in the FROM clause? If yes, all is good. If no, you probably need another, nested subselect. (Or, you need to restructure the query with this scope rule in mind.)


1. This is what Keith was telling you above. The term "<u>[projection](https://en.wikipedia.org/wiki/Projection_(relational_algebra\))</u>" is from relational algebra, part of database theory since it was invented.