Обсуждение: Column aliases for GROUP BY and HAVING

(Postgres version 8.2.4)

Trying to understand GROUP BY, I'm reading on
<http://www.postgresql.org/docs/8.4/interactive/queries-table-expressions.html>.
Consider this query:

=> select x, sum(y) from test1 group by x order by x;
 x | sum
---+-----
 a |   4
 b |   5
 c |   2
(3 rows)

I understand what's happening here, all is fine. So I play with HAVING:

=> select x, sum(y) from test1 group by x having sum(y)>3 order by x;
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)


But this confuses me:

=> select x, sum(y) as foo from test1 group by x having foo>3 order by x;
ERROR:  column "foo" does not exist
LINE 1: ...ect x, sum(y) as foo from test1 group by x having foo>3 orde...

Why isn't it possible to refer to a column alias in HAVING?


--
- Rikard

Rikard Bosnjakovic <rikard.bosnjakovic@gmail.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

I think because you are not selecting the column you are referring to.
Try:

HAVING SUM(y)>3

Kris


On 25-Nov-09, at 14:20, Rikard Bosnjakovic
<rikard.bosnjakovic@gmail.com> wrote:

> (Postgres version 8.2.4)
>
> Trying to understand GROUP BY, I'm reading on
> <http://www.postgresql.org/docs/8.4/interactive/queries-table-expressions.html
> >.
> Consider this query:
>
> => select x, sum(y) from test1 group by x order by x;
> x | sum
> ---+-----
> a |   4
> b |   5
> c |   2
> (3 rows)
>
> I understand what's happening here, all is fine. So I play with
> HAVING:
>
> => select x, sum(y) from test1 group by x having sum(y)>3 order by x;
> x | sum
> ---+-----
> a |   4
> b |   5
> (2 rows)
>
>
> But this confuses me:
>
> => select x, sum(y) as foo from test1 group by x having foo>3 order
> by x;
> ERROR:  column "foo" does not exist
> LINE 1: ...ect x, sum(y) as foo from test1 group by x having foo>3
> orde...
>
> Why isn't it possible to refer to a column alias in HAVING?
>
>
> --
> - Rikard
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice

On Wed, Nov 25, 2009 at 20:35, Tom Lane <tgl@sss.pgh.pa.us> wrote:

[...]
> 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.

Thank you for the explanation. I didn't fully understand the gory
details, but it's of use to know that I cannot refer to an alias.


--
- Rikard