Using shortcuts for column names

Get Help SQL
#1

Hi everyone! I’m following the SQL course here on codecademy.

I learned that I can avoit to write againt the column name using numbers, but sometimes it works and sometime it doesn’t, i can get why.

For example this one is working:

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

And this one is not working:

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

Can anyone explain me why? Thanks!

#2

At first I was going to say it’s because you’re using a condition on an aggregate. But, I think it’s b/c the HAVING clause is executed before the SELECT clause in the order of operations (behind the scenes).

See:
https://dba.stackexchange.com/questions/225874/using-column-alias-in-a-where-clause-doesnt-work

#3

I think that particular expression, 2 > 500 is simply going to evaluate to a boolean result; I don’t know if that’s the case in every sql flavour but it’s always best to avoid ambiguity. In short I don’t think you can use column numbers when you’re using operators that can act on numbers.

If you’ve got a rather complicated expression in SELECT then aliasing the column might be a nice option, for example-

SELECT
    location,
    ROUND(AVG(employees), 2) AS average_size
FROM startups
GROUP BY location
HAVING average_size > 500;
2 Likes
#4

HAVING is a condition/a grouped query (b/c it filters groups not rows) that uses an aggregate.
When you have something like HAVING 2 > 500 won’t work b/c it is executed before the SELECT statement.
In Postgres for example:

" Since the HAVING clause is evaluated before the SELECT clause, you cannot use aliases in the HAVING clause. Because at the time of evaluating the HAVING clause, the column aliases specified in the SELECT clause are not available."

(From that, I take it to mean column numbers.)

And, here.

1 Like
#5

That can vary based on which SQL you’re using, in SQLite aliases in HAVING are perfectly valid and it would work in this lesson. Then again SQLite does a few funny things so you might be safer avoiding that option just in case you have to switch between flavours.

1 Like