pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Joseph Shraibman <jks@selectacast.net>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: WHERE on an alias
Date: Fri, 24 Aug 2001 16:18:50 -0400
Message-ID: <3B86B6AA.3020309@selectacast.net> (raw)
References: <web-107962@davinci.ethosmedia.com>


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




view thread (9+ messages)  latest in thread

Message-ID: <3B86B6AA.3020309@selectacast.net>
Permalink:  ../3B86B6AA.3020309@selectacast.net/
Also on:    postgresql.org/message-id/3B86B6AA.3020309@selectacast.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jks@selectacast.net, josh@agliodbs.com
  Subject: Re: WHERE on an alias
  In-Reply-To: <3B86B6AA.3020309@selectacast.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox