(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

Search Discussions

  • Tom Lane at Nov 25, 2009 at 7:35 pm

    Rikard Bosnjakovic 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
  • Rikard Bosnjakovic at Nov 26, 2009 at 1:14 am
    On Wed, Nov 25, 2009 at 20:35, Tom Lane 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
  • Kris Kewley at Nov 26, 2009 at 12:47 am
    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
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedNov 25, '09 at 7:20p
activeNov 26, '09 at 1:14a
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase