Re: Using an ALIAS in WHERE clause

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

"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