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

Language Help SQL
1

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?

2

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.

1 Like
3

Thank you! I couldn’t figure it out.

1 Like