Thread: WHERE on an alias
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. -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
That was just an example. My code doesn't use group by, it uses a subselect. And I checked and having doesn't work with aliases either. Stephan Szabo wrote: > On Fri, 24 Aug 2001, Joseph Shraibman wrote: > > >>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. >> > > Somewhat unrelated question, are you sure you want where and not having? > IIRC, where is going to select rows before things like the group by > occur, and that doesn't seem to be what you'd want here, right? > -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
On Fri, 24 Aug 2001, Joseph Shraibman wrote: > 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. Somewhat unrelated question, are you sure you want where and not having? IIRC, where is going to select rows before things like the group by occur, and that doesn't seem to be what you'd want here, right?
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
Joseph, > playpen=# SELECT a, sum(b) as dsum > playpen-# FROM taba > playpen-# GROUP BY a > playpen-# HAVING dsum > 5; Sorry, my mistake: SELECT a, sum(b) as dsum FROM taba GROUP BY a HAVING sum(b) > 5; I had the impression that aliases for aggregates were allowed in Postgres HAVING clauses. Apparently not. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
On Fri, 24 Aug 2001, Joseph Shraibman wrote: > That was just an example. My code doesn't use group by, it uses a subselect. And I > checked and having doesn't work with aliases either. Okay. I know why WHERE doesn't support aliases, but was a little confused by the example. The reason for that is that the select list isn't worked out until after the rows are qualified and it technically fits spec. I think it might fall into something like (untested):select * from (select a, (...) as subsel from taba) as foo where foo.subsel>5; to fit standard, but that's pretty ugly...
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; -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Joseph Shraibman <jks@selectacast.net> writes:
> 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?
Because WHERE is computed before the select's output list is.
Strictly speaking you shouldn't be able to GROUP on an alias either (the
SQL spec doesn't allow it). We accept that for historical reasons only,
ie, our interpretation of GROUP used to be wrong and we didn't want to
break applications that relied on the wrong interpretation.
Note that writing a GROUP on an alias does *not* mean the alias is only
computed once. It saves no computation, only writing out the expression
twice.
> I have a subselect that
> explain shows is being run twice if I have to put it in the WHERE clause.
Possibly you could restructure your query into something with a
subselect in the FROM clause?
regards, tom lane
----- Original Message ----- From: "Joseph Shraibman" <jks@selectacast.net> Subject: Re: [SQL] WHERE on an alias > If I try to put a distinct on in my subselect int the from I get: > > ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions > > what does that mean? You need to (at least) ORDER BY the expression you want to have the DISTINCT ON. Tom's example would work fine: > > select distinct on (ml.f1,ml.f2) * from ut,ml > > where ut.f1 = ml.f1 and ut.f2 = ml.f2 > > order by ml.f1, ml.f2, ml.f3 desc; Wrong: select distinct on (ml.f1,ml.f2) * from ut,ml where ut.f1 = ml.f1 and ut.f2 = ml.f2 order by ml.f2, ml.f1, ml.f3 desc; Andre