Skip site navigation (1) Skip section navigation (2)

Re: Using an ALIAS in WHERE clause

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Ron St(dot)Pierre" <rstpierre(at)syscor(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Using an ALIAS in WHERE clause
Date: 2002-11-29 00:38:00
Message-ID: 18195.1038530280@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
"Ron St.Pierre" <rstpierre(at)syscor(dot)com> writes:
> I'm using a query with similar functionality to the following:
>   SELECT  id,
>   sum(hours) AS totalhours
>   FROM mytable
>   WHERE totalhours > 50;

> I get the following error:
>   Attribute 'totalhours' not found.

> Am I not allowed to use an alias here?

No.  Evaluation of the WHERE clause logically precedes evaluation of the
SELECT list, so it's really quite nonsensical to expect SELECT outputs
to be available in WHERE.  Furthermore, in this particular case you'd be
introducing an aggregate function into WHERE, which is also nonsensical.
Aggregate results have to be checked in HAVING, which acts after
grouping/aggregation, whereas WHERE filters rows beforehand.  You may
find it helpful to read
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/tutorial-agg.html

(BTW, I assume there's really a "GROUP BY id" in there?  If not, you've
got other problems.)

> If not, how can I get my desired output?

Like so:

SELECT  id,
  sum(hours) AS totalhours
  FROM mytable
  GROUP BY id
  HAVING sum(hours) > 50;

If you really can't be bothered to write sum() twice, you could consider
a two-level SELECT:

SELECT * FROM
  (SELECT  id,
    sum(hours) AS totalhours
   FROM mytable
   GROUP BY id) ss
WHERE totalhours > 50;

The sub-select has its own aggregation pipeline that acts before the
outer select does anything, so the basic rule of "no aggregate
references in WHERE" is not being violated here.

			regards, tom lane

In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2002-11-29 01:04:10
Subject: Re: Server v7.3RC2 Dies
Previous:From: Tycho FruruDate: 2002-11-29 00:30:44
Subject: Re: Using an ALIAS in WHERE clause

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group