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@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com