Re: WHERE on an alias

playpen=# SELECT a, sum(b) as dsum
playpen-# FROM taba
playpen-# GROUP BY a
playpen-# HAVING dsum > 5;
ERROR: Attribute 'dsum' not found

HAVING does not work either. In fact this example is in the pg docs for SELECT:
SELECT kind, SUM(len) AS total
FROM films
GROUP BY kind
HAVING SUM(len) < INTERVAL '5 hour';

Josh Berkus wrote:
> Joseph,
>
>
>>playpen=# select a, sum(b) as dsum from taba where dsum > 5 group
>>by(a);
>>ERROR: Attribute 'dsum' not found
>>
>>Why can we GROUP BY on an alias but not do a WHERE on an alias? I
>>have a subselect that
>>explain shows is being run twice if I have to put it in the WHERE
>>clause.
>>
>
> Ah, but you are not trying to do a WHERE on an alias ... you are trying
> to do a WHERE on an *aggregate*, which is a different thing. Consult
> your favorite SQL manual; the construction you want is:
>
> SELECT a, sum(b) as dsum
> FROM taba
> GROUP BY a
> HAVING dsum > 5;
>

>

--
Joseph Shraibman
jks(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com