Aggregate functions - referencing columns by number - why doesn't it work

in the project at the end of the aggregate functions unit and one of the exercises resulted in the following code:
SELECT location, AVG(employees)
FROM startups
GROUP BY location
HAVING AVG(employees) > 500;

I can replace the column reference in the “group by” line, but not in the “having” line.

This works:
SELECT location, AVG(employees)
FROM startups
GROUP BY 1
HAVING AVG(employees) > 500;

This does not work:
SELECT location, AVG(employees)
FROM startups
GROUP BY 1
HAVING 2 > 500;

Why?

Because HAVING is evaluated first, then SELECT. So, the server doesn’t know what aliases you’re referring to. Research “order of execution of a SQL query” for reference.

2 Likes

Thank you! I couldn’t figure it out.

1 Like

This wasn’t my question, and it was posed a long time ago, but this really drives home the point that knowing the order with which clauses in queries are executed is vital to improving one’s SQL acumen. I remember how surprised I was that the SELECT is executed after several other clauses, despite where it shows up in a query.