Re: How to use result column names in having cause

[Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>]

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