Re: Column aliases for GROUP BY and HAVING

Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com> writes:
> Why isn't it possible to refer to a column alias in HAVING?

According to the SQL standard you aren't allowed to refer to an output
column alias in *any* of those clauses. It's nonsensical because the
output columns aren't (logically speaking) computed until after the
GROUP BY/HAVING computations have been done. For instance, you'd
probably not be happy if this failed with a zero-divide error:

SELECT 1/x, avg(y) FROM tab GROUP BY x HAVING x <> 0;

In practice PG allows you to refer to output column aliases as simple
GROUP BY and ORDER BY entries, though not as part of expressions.
This is historical rather than something we'd be likely to do if we
were starting over, though I admit it does save typing in a lot of
cases.

HAVING is not included because (a) it wasn't historically, and (b)
the use-case for a bare column alias in HAVING would be pretty small
anyway. Your example wouldn't work even if HAVING acted the same
as GROUP BY/ORDER BY, since you didn't just write the alias but
tried to compare it to something else.

regards, tom lane