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.