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
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