'HAVING' filter option problem

Hello, everyone.

I’ve been walking through the tutorials and interactive projects fine until I start trying to combine what I’ve learned with the answer to the current query.

Here is a link to the project: https://www.codecademy.com/courses/learn-sql/projects/fakeapps
Specifically, my question relates to task 14.

The task is to find the average size of a start-up in each location, with average sizes above 500.

This is the correct answer according to the ‘hints’ dropdown:

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

Here is what I have typed to make the resulting table read clearer which produces no result:

SELECT location AS LOCATION, ROUND(AVG(employees), 2) AS EMPLOYEES
FROM startups
GROUP BY 1
HAVING EMPLOYEES > 500;

The ‘HAVING’ function doesn’t seem to be accepting the alias. Is this correct?

Thank you for any help with this.

Frankie

@tintin85,

The short answer is because in SQL, the HAVING statement is usually executed before the SELECT statement. Thus, the alias hasn’t been created yet when it’s being called by the HAVING statement. For more information on the (usual) order of execution, see my answer to this post.

However, the unusual thing here is that I was able to use an alias in the HAVING statement here on CC by using an alias that wasn’t the same is the original column name the aggregate function was called on:

I didn’t expect this, as I thought MySQL was the only RDBMS that allowed aliases in HAVING statements (see this discussion). My suspicion is that it may have something to do with SQLite’s query optimization (see this discussion if you are interested), but I’m not entirely sure.

Either way, to be entirely safe, you should stick to syntax in the hint (HAVING AVG(employees) > 500) as most RDBMSs won’t allow your column aliases in the HAVING statement.

1 Like

Thank you, el_cocodrilo. This is invaluable information. I suppose the order would have been explained to me the further I get in the course, but it’s great to know this now.

I’ve read the comments in the links you provided and it’s interesting that SELECT is so far down the order of execution.

Thanks again!

1 Like