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

On 12/09/2011 02:48 PM, Jack Christensen wrote:
> CREATE TABLE people(
> id serial PRIMARY KEY,
> name varchar NOT NULL
> );
>
> INSERT INTO people(name) VALUES('Adam'), ('Adam'), ('Adam'), ('Bill'),
> ('Sam'), ('Joe'), ('Joe');
>
> SELECT name, count(*), random()
> FROM people
> GROUP BY name;
>
>
> I would expect this query to cause an error because of random(). I ran
> into this using an array produced by a subquery as a column in the
> select of an aggregate query, but I was able to boil it down to this
> contrived example. Shouldn't any expression that is not in the group by
> or an aggregate function be rejected?
>
> What am I not understanding?

http://www.postgresql.org/docs/9.0/interactive/sql-select.html#SQL-GROUPBY
"
Aggregate functions, if any are used, are computed across all rows
making up each group, producing a separate value for each group (whereas
without GROUP BY, an aggregate produces a single value computed across
all the selected rows). When GROUP BY is present, it is not valid for
the SELECT list expressions to refer to ungrouped columns except within
aggregate functions, since there would be more than one possible value
to return for an ungrouped column."

My guess, random() does not refer to a column, so it falls outside the
above criteria.

>
> Thanks.
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com