Re: SELECT, GROUP BY, and aggregates

To lower the amount of time spent copy pasting aggregate column names,
it's probably worth noting Postgres will allow you to short cut that
with the column position.  For example:

select long_column_name_A, long_column_name_b, count(1)
from foo
group by 1,2
order by 1,2

This works just fine.  It's not in the spec, but postgres supports it.
I'll leave it to others to argue about it being a best practice or
not.


On Fri, Feb 13, 2015 at 1:57 PM, Bill Moran <wmoran@potentialtech.com> wrote:
> On Fri, 13 Feb 2015 10:48:13 -0800
> Jeff Janes <jeff.janes@gmail.com> wrote:
>
>> On Fri, Feb 13, 2015 at 10:26 AM, Bill Moran <wmoran@potentialtech.com>
>> wrote:
>>
>> > > Ryan Delaney <ryan.delaney@gmail.com> writes:
>> > > > Why couldn't an RDBMS such as postgres interpret a SELECT that omits
>> > the GROUP
>> > > > BY as implicitly grouping by all the columns that aren't part of an
>> > aggregate?
>> >
>> > I'm Mr. Curious today ...
>> >
>> > Why would you think that such a thing is necessary or desirable? Simply
>> > add the
>> > columns to the GROUP BY clause and make the request unambiguous.
>>
>> Where would the ambiguity be?
>
> With a large, complex query, trying to visually read through a list of
> column selections to figure out which ones _aren't_ aggregated and will
> be auto-GROUP-BYed would be ... tedious and error prone at best.
>
> You're right, though, it wouldn't be "ambiguous" ... that was a poor
> choice of words on my part.
>
>> I waste an inordinate amount of time retyping select lists over into the
>> group by list, or copying and pasting and then deleting the aggregate
>> clauses.
>
> Interesting ... I've never kept accurate track of the time I spend doing
> things like that, but "inordinate" seems like quite a lot.
>
> In my case, I'm a developer so I would tend toward creating code on the
> client side that automatically compiled the GROUP BY clause if I found
> that scenarios like you describe were happening frequently. Of course,
> that doesn't help a data anaylyst who's just writing queries
>
>> It is an entirely pointless exercise.  I can't fault PostgreSQL
>> for following the standard, but its too bad the standards aren't more
>> sensible.
>
> I can't speak to the standard and it's reasons for doing this, but there
> are certainly some whacko things in the standard.
>
> Thanks for the response.
>
> --
> Bill Moran
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general