On Fri, 31 Mar 2006, Andrus wrote: > >> In real application I have long expression instead of 123 and do'nt want > >> repeat this expression in HAVING clause. > > > > You have to repeat the expression. "AS" changes the output name, it > > can't be used either in the where clause or any other limiting factor > > like 'having': > > Doc about HAVING condition says: > > Each column referenced in condition must unambiguously reference a grouping > colum > > HAVING x> AVG(bar) unambiguously references to a grouping column x IIRC technically the query is invalid, because group by isn't supposed to run on the output of select entries (as I think is stated by "Each <grouping column reference> shall unambiguously reference a column of the table resulting from the <from clause>.") and I'd guess this is a side effect of allowing group by to work on the table resulting from the select list as well. I think the SQL way of writing this is to use a subselect and do two levels (ie, generate a subselect that gives the table you want to group and use it in the from clause of the outer query that does the grouping).
