Re: Using an ALIAS in WHERE clause

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? If not, how can I get my desired
output?

Thanks

Note: I'm using postgresql 7.2

--
Ron St.Pierre
Syscor R&D
tel: 250-361-1681
email: rstpierre(at)syscor(dot)com

On Fri, 2002-11-29 at 01:17, Ron St.Pierre wrote:
> 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? If not, how can I get my desired
> output?

select id, sum(hours) as totalhours
from mytable
group by id
having totalhours > 50

'where' is for tuple selection criteria
'having' is for group selection criteria
i suppose you want to have the total number of hours per id (therefore
we need to group by id.

Does this help ?
Tycho

"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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
[snip]
>
> 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.
>

I was thinking of an related thing, how do we handle queries like these
(actual used query):

select
o.id as order_id,
o.cust_id,
i.id as invoice_id,
i.created::date as invoice_created,
extract('days' from (now() - i.created)) as days_overdue,
c.type,
c.status
from
order o,
invoice i,
cust c
where
(o.ordersystem = 0) and
(o.status = 3 and o.substatus = 3) and
(i.order_id = o.id) and
(c.id = o.cust_id) and
(c.account_expires >= now()) and

((c.type & (1|4|8)::int8) = 0) and /* some int8 flags */
((c.status & (2|4)::int8) = 0) and /* some other int8 flags */

->extract('days' from (now() - i.created)) >= 20

order by
dagar_overdue desc
;

Is the days_overdue calculated twice, if it is, how can i get the effect
of replacing the where condition with days_overdue? Like:

select
days_overdue
...
where
(extract('days' from (now() - i.created)) AS days_overdue) >= 20

Hmm. Well it's not that big of an hassle but it'd look nice!
Not sure of how big of a performance win it would be, the extract thing
shouldn't be that slow, right?

Regards
Magnus Naeslund

"Magnus Naeslund\(f\)" <mag(at)fbab(dot)net> writes:
> select
> ...
> extract('days' from (now() - i.created)) as days_overdue,
> ...
> where
> ...
> extract('days' from (now() - i.created)) >= 20

> Is the days_overdue calculated twice,

Yes.

> Not sure of how big of a performance win it would be, the extract thing
> shouldn't be that slow, right?

In general I think this is useless micro-optimization ;-). There are
few functions in SQL that are expensive enough that it's worth worrying
about calling them twice per row.

If you have a case where it really does matter (super-expensive
user-defined function, perhaps) you could probably do something with
the multi-level-SELECT technique I illustrated.

Years ago, someone at Berkeley did a thesis about planning in the
presence of expensive functions, and the remnants of that thesis are
still in the Postgres sources --- but it's dead code and would not be
easy to resurrect. I personally doubt it could be worth the trouble.

regards, tom lane