Re: Why does aggregate query allow select of non-group by or aggregate values?

On 12/9/2011 4:57 PM, David Johnston wrote:
> Functions are evaluated once for each row that it generated by the
> surrounding query. This is particularly useful if the function in question
> takes an aggregate as an input:
>
> SELECT col1, array_processing_function( ARRAY_AGG( col2 ) )
> FROM table
> GROUP BY col1;
>
> Without this particular behavior you would need to sub-query.
>
> > From a layman's perspective the reason why you cannot use non-aggregates
> outside of GROUP BY it that it is ambiguous as to what value to output; with
> an uncorrelated function call that is not the case.
>
> David J.
>
>
>
>
Thanks. This makes sense now. I also went back to the original query
that provoked this question. It had a correlated subquery in the select
statement. I thought that this could yield ambiguous results. But when I
examined it closely, all the correlated fields were included in the
group by of the outer query, and when I tried to use a non-grouped
column from the outer query I correctly got a ERROR: subquery uses
ungrouped column "foo" from outer query

Thanks again.

--
Jack Christensen
jackc(at)hylesanderson(dot)edu