using subquery's column alias as a property in main query

Question

i want to know if the main query can see the alias, here's an example:

SELECT AVG(values)
FROM(
  SELECT SUM(a1) AS values
  FROM tableX
)

Does the first query see the alias "values"?

Answer 1

Does the first query see the alias "values"?

Yes, it does. The subquery creates a derived table, and aliases act as column names in that context. However, standard SQL requires that you give an alias to the subquery.

So:

SELECT AVG(vals)
FROM(
    SELECT SUM(a1) AS vals
    FROM tableX
) t --> alias of the subquery

Side notes:

• values is a language keyword, hence not a good choice for a column name; I renamed it to vals in the query

• Your example is really contrived; the subquery always returns one row, so aggregating again in the outer query makes little sense: this is guaranteed to return the same value as that of the subquery. A more useful example would put a group by clause in the subquery, like so

SELECT AVG(vals)
FROM(
    SELECT SUM(a1) AS vals
    FROM tableX
    GROUP BY id
) t