Sql Excel : Subqueries for Naming Variables

When it comes to naming variables, SQL has a shortcoming. The following is not
syntactically correct in most SQL dialects:
SELECT totpop as pop, pop + 1
The SELECT statement names columns, but these names cannot be used again in
the same clause. Because queries should be at least somewhat understandable to
humans, as well as database engines, this is a real shortcoming. Complicated
expressions should have names.
Fortunately, subqueries provide a solution. The earlier query that summarized zip
codes by population greater than 10,000 and greater than 1,000 could instead use
a subquery that is clearer about what is happening:
SELECT zc.stab,
SUM(is_pop_10000) as num_10000,
SUM(is_pop_1000) as num_1000,
SUM(is_pop_10000 * totpop) as pop_10000,
SUM(is_pop_1000 * totpop) as pop_1000
FROM (SELECT zc.*,
(CASE WHEN totpop > 10000 THEN 1 ELSE 0
END) as is_pop_10000,
(CASE WHEN totpop > 1000 THEN 1 ELSE 0
END) as is_pop_1000
FROM ZipCensus zc
) zc
GROUP BY zc.stab
This version of the query uses two indicator variables, IS_POP_10000 and
IS_POP_1000. These take on the value of 0 or 1, depending on whether or not the
population is greater than 10,000 or 1,000. The query then sums the indicators to
get the counts, and sums the product of the indicator and the population to get the
population count.