Are you saying you can't use column aliases in group by? What version of Postgres are you using? I just tried it in 11.5 and it worked:

    # select cust_id as c, sum(avail_balance) as b from account group by c order by b;

I was thinking of Oracle, where aliases are evaluated at column protection time, so after grouping but before ordering.

Interesting. It doesn't work in MSSQL, and I understand that's correct (ie. isn't allowed) per the standard.

Huh - I guess I never thought about it. It makes sense to disallow it, though - column aliases are there to rename complex expressions, which you probably _shouldn't_ be grouping on anyway.

> which you probably _shouldn't_ be grouping on anyway.

This is frequently unavoidable, though. Or more precisely: it could be avoided with a sane database design, but the databases on which I have to work for my day job are the precise opposite of "well-designed", so grouping on complex expressions is unfortunately an inevitability.

That's true - I can definitely imagine having to group on something like "concat(lastname + ', ' + firstname)".

I think it's for other reasons (and grouping on expressions is quite reasonable anyway).

It's (IIRC!) something to do with the situation of

  select x + y as x 
  from ...
  group by x

which x are we talking about? (Logically that example is crap because only the alias x makes sense, but something like that anyway).

I always, always forgot what column aliases I can use where. Thanks for the reminder.