Skip site navigation (1) Skip section navigation (2)

Re: Improving GROUP BY?

From: John Seberg <johnseberg(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Improving GROUP BY?
Date: 2005-09-30 13:31:07
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
--- Oliver Siegmar <oliver(at)siegmar(dot)net> wrote:

> Hello,
> as you all may know, the GROUP BY clause in
> PostgreSQL has to be parameterized 
> with all fields that appear in the SELECT clause
> (except aggregates).
> So
> SELECT   id, foo, sum(bar)
> FROM     table1
> GROUP BY id, foo;
> is perfectly valid, where
> SELECT   id, foo, sum(bar)
> FROM     table1
> GROUP BY id;
> would fail with something like '"foo" must appear in
> the GROUP BY clause or be 
> used in an aggregate function'.
> I understand that behaviour in the case that the
> 'id' field wouldn't be a 
> primary (or unique) identifier of the table.
> So my questions are
> - Would it be possible to add a feature to
> PostgreSQL to allow grouping by 
> unique identifiers only?

There would seem to be no point in grouping by unique
ids - by definition, there would only be one member in
each group.

> - Is this requirement (all fields in GROUP BY) based
> on the SQL standard? If 
> yes, by which version?

I don't know about standards, but, this behavior is
common among several vendors I have used. If you want
to see one of the values of foo, in your example, use
min() or max(). Just be mindful that other values of
foo may exist in the grouping.

So, I fail to see your "improvement". I like the way
GROUP BY works just fine.

Yahoo! Mail - PC Magazine Editors' Choice 2005

In response to

pgsql-general by date

Next:From: Oliver SiegmarDate: 2005-09-30 13:38:20
Subject: Re: Improving GROUP BY?
Previous:From: Peter EisentrautDate: 2005-09-30 13:23:29
Subject: Re: Improving GROUP BY?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group