-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Jack Christensen Sent: Friday, December 09, 2011 5:48 PM To: pgsql-general@xxxxxxxxxxxxxx Subject: Why does aggregate query allow select of non-group by or aggregate values? 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? Thanks. ---------------------------------------------------------------------------- ------- 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
