Column aliases for GROUP BY and HAVING

From: Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Column aliases for GROUP BY and HAVING
Date: 2009-11-25 19:20:04
Message-ID: d9e88eaf0911251120g5f2e1506k900e53102ef9a1e1@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

(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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2009-11-25 19:35:09 Re: Column aliases for GROUP BY and HAVING
Previous Message Dara Olson 2009-11-25 13:51:17 Re: sum divided by count ends in zero