Re: Group By and wildcards...

On Sat, Feb 19, 2005 at 12:40:40 -0500,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Bruno Wolff III <bruno(at)wolff(dot)to> writes:
> > Jon Lapham <lapham(at)jandr(dot)org> wrote:
> >> When using queries with aggregate functions, is there any way to not
> >> have to have to explicitly write all the columns names after the GROUP
> >> BY ? I would like to use a wildcard "*".
>
> > Don't those tables have primary keys? Grouping by the primay key of each
> > table will produce the same result set as grouping by all of the columns.
>
> Unfortunately, PG will still make him GROUP BY everything he wants to
> use as a non-aggregated output column. This behavior is per SQL92
> spec. SQL99 added some verbiage to the effect that you only need to
> GROUP BY columns that the rest are functionally dependent on (this
> covers primary keys and some other cases); but we haven't got round
> to implementing that extension.

I forgot about that. However, if maintainance is the most important
consideration, then it is possible to use only the primary keys
to do the grouping and then join that result back to the original
tables to pick up the other columns. It should be possible to do
this without explicitly naming all of the columns. It will be slower
and more complicated, but this might be an acceptable trade off.